Jerry Nixon @Work: Can you GROUP BY a CASE statement in SQL 2005?

Jerry Nixon on Windows

Tuesday, April 1, 2008

Can you GROUP BY a CASE statement in SQL 2005?

Yes, you can:

create table x (col1 int, col2 int)
GO

insert into x select 1, 1
insert into x select 2, 1
insert into x select 3, 1
insert into x select 4, 1
insert into x select 5, 1
GO

select
count(*) as count
,(case
when col1 % 2 = 0 then 'Even'
else 'Odd' end) as what
from x
group by (case
when col1 % 2 = 0 then 'Even'
else 'Odd' end)
GO

drop table x

/*

output

count what
----------- ----
2 Even
3 Odd

*/