Group By and Case Statements or Other SQL Functions

Posted by on February 28, 2008

One thing I forget to do often is to include the actual item I’m returning in the Select Statement in the Group By. Meaning , if I use a case statement of some function to change the value that I’m returning, then I should include that and not the base column name in the Group By part of the select statement


/*
If you write something like this and the Col2 has multiple 'Unique' Values
then you will get a row for each value distinct Col2 value that says 'Other'
*/SELECT [Col1]
,Case When [Col2] = 10 then 'Ten'
Else 'Other' End as [LameExample]
,Count(*) as CNT
FROM [dbo].[lameTableExample]
GROUP BY [Col1], [Col2]

-- This Example will return Only two rows

SELECT [Col1]
,Case When [Col2] = 10 then 'Ten'
Else 'Other' End as [LameExample]
,Count(*) as CNT
FROM [dbo].[lameTableExample]
GROUP BY [Col1]
, Case When [Col2] = 10 then 'Ten'
Else 'Other' End

Select Case has a simple overview of using CASE in a SQL Query with the SUM() function.

Tiny URL for this post:
 

Share the joy
      
SQL Server, SSMS, t-sql, , , , ,

Comments are closed.