Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #546

Re: How do I use GROUP BY when I am using CASE?

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

How do I use GROUP BY when I am using CASE? BobRoyAce <broy@omegasoftwareinc.com> - 2011-07-18 19:40 -0700
  Re: How do I use GROUP BY when I am using CASE? Erland Sommarskog <esquel@sommarskog.se> - 2011-07-19 09:22 +0200
    Re: How do I use GROUP BY when I am using CASE? Herb <HERB_VOGEL@JBHUNT.COM> - 2011-07-19 06:15 -0700

csiph-web