89f6187e   
   From: esquel@sommarskog.se   
      
   BobRoyAce (broy@omegasoftwareinc.com) writes:   
   > Let's say that I want to have a query like the following:   
   >   
   > SELECT Field1, Field2,   
   >   CASE Field3   
   >     WHEN 1 THEN 'GOOD'   
   >     WHEN 2 THEN 'BAD'   
   >     ELSE 'UGLY'   
   >   END AS FieldResult,   
   >   SUM(Field4) AS TotalField4   
   > FROM Table1   
   > GROUP BY Field1, Field2, FieldResult   
   >   
   > How do I accomplish something like this? The query above does not work   
   > as it does not recognize a field called FieldResult.   
   >   
   > I got it to work by changing the query as shown below, but I am   
   > wondering if there is a smarter/better way to do it.   
      
   You cannot refer to a column alias elsewhere in the query but in the ORDER   
   BY clause. This is because the SELECT list is the next-to-last to be   
   computed, and the columns are defined all at once. (Actually some products   
   like Access and Teradata permit this, but this in conflict with the SQL   
   standard.)   
      
   The alternative to repeat the expression in the GROUP BY clause, is to use a   
   CTE (Common Table Expression) or a derived table. For instanace:   
      
   WITH CTE AS (   
      SELECT Field1, Field2,   
             CASE Field3 WHEN 1 THEN 'GOOD'   
                         WHEN 2 THEN 'BAD'   
                         ELSE 'UGLY'   
             END AS FieldResult   
      FROM   tbl   
   )   
   SELECT Field1, Field2, FieldResult, SUM(Field4) AS TotalField4   
   FROM   Table1   
   GROUP  BY Field1, Field2, FieldResult   
      
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Links for SQL Server Books Online:   
   SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx   
   SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)