Why doesn't this work? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Why doesn't this work?

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-03-10 : 09:44:54

Can't CASE create a column, with values in there, and group by the name of the column, and count that??

My first resort was to rename the group by clause as the entire case statement. That doesn't work

select
case when report like '%BPL%' then 'BPL'
when report like '%OFAB%' then 'OFAB'
when report like '%USD%' then 'USD'
when report like '%PRSV%' then 'PRSV'
when report like '%Prsv Bpos%' then 'PRSV BPOS'
else 'other'
end area
,COUNT(*)
from ReportLog
group by area
order by COUNT(*) desc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-10 : 09:52:47

you can't group by a column alias. However you can use derived table or CTE for the query and perform the GROUP BY at the outer query


SELECT area 
      ,COUNT(*)
FROM
(
select
case when report like '%BPL%' then 'BPL'
when report like '%OFAB%' then 'OFAB'
when report like '%USD%' then 'USD'
when report like '%PRSV%' then 'PRSV'
when report like '%Prsv Bpos%' then 'PRSV BPOS'
else 'other'
end area
from ReportLog
) a
group by area
order by COUNT(*) desc

KH
[spoiler]Time is always against us[/spoiler]

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-03-10 : 10:00:15

Genius!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-10 : 10:49:50

or you need to use the entire expression in the GROUP BY Clause

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources