CASE statement aggregate question

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 2005 Forums

Transact-SQL (2005)

CASE statement aggregate question

Author

Topic

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-05-01 : 11:51:28

Query:

select platform, count(platform) as OS_Count
from dsm_hardware_basic
group by platform
order by platform desc

Returns a table like:
Platform | OS_Count
Windows XP 1000
Windows 7 500
Linux 50
Windows Server 5

I want to be able to use a case statement (or something else) to lump all %Windows% platforms into a Windows and count them all together so the output would be

Returns a table like:
Platform | OS_Count
Windows 1505
Linux 50

Any ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 11:57:53

[code]
select case when platform like 'windows%' then 'Windows' else platform end as Plarform, count(platform) as OS_Count
from dsm_hardware_basic
group by case when platform like 'windows%' then 'Windows' else platform end
order by platform desc
[/code]

I would have added a mapping table to group them all by means of groupname attribute to make it scalable if you need several such custom groups

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

darms21
Yak Posting Veteran

54 Posts

Posted - 2012-05-01 : 12:01:49

Ahh...very good. I was not aware of the group by case ability.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-01 : 12:06:59

welcome
As i told this method is not very scalable so if you want to group data onto lots of similar groups better to use a mapping table with groupname and detailname columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources