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

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

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

Show all headers | View raw

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.

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, CASE Field3
    WHEN 1 THEN 'GOOD'
    WHEN 2 THEN 'BAD'
    ELSE 'UGLY'
  END

Back to comp.databases.ms-sqlserver | Previous | Next — 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