February 21, 2012 at 12:57 pm
Is there anyway to GROUP BY a CASE statement (or achieve the same results with a different method) in the same query? ie.
SELECT
CASE
WHEN abb.[field1] is null
THEN 'Yes'
ELSE 'No'
END AS [newfield],
sum(table2.field3)
FROM table1 LEFT JOIN table2 AS abb ON table1.field2 = table2.field2
GROUP BY newfield
February 21, 2012 at 1:15 pm
Unsure on what you are trying to do!
Are you trying to find out the number of Null and Not Null rows? If yes, then this might help you
SELECT
[YesCount] = SUM( CASE WHEN abb.[field1] IS NULL THEN 1 ELSE 0 END )
,[NoCount] = SUM( CASE WHEN abb.[field1] IS NOT NULL THEN 1 ELSE 0 END )
FROM table1
LEFT JOIN table2 AS abb
ON table1.field2 = table2.field2
February 21, 2012 at 1:16 pm
Also read the following article on the etiquettes to be followed while posting questions. Trust me, this will reap you rich benefits!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 21, 2012 at 1:24 pm
guerillaunit (2/21/2012)
Is there anyway to GROUP BY a CASE statement (or achieve the same results with a different method) in the same query? ie.SELECT
CASE
WHEN abb.[field1] is null
THEN 'Yes'
ELSE 'No'
END AS [newfield],
sum(table2.field3)
FROM table1 LEFT JOIN table2 AS abb ON table1.field2 = table2.field2
GROUP BY newfield
Besides the fact that it may not be the best way to do this if the above query is not just for testing, here you go:
SELECT
CASE
WHEN abb.[field1] is null
THEN 'Yes'
ELSE 'No'
END AS [newfield],
sum(table2.field3)
FROM table1 LEFT JOIN table2 AS abb ON table1.field2 = table2.field2
GROUP BY CASE
WHEN abb.[field1] is null
THEN 'Yes'
ELSE 'No'
END
Simple, just copy the code from CASE to END leaving out the AS [newfield].
Jared
CE - Microsoft
February 21, 2012 at 1:49 pm
Xixi sqlknowitall
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply