SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CASE statement aggregate question		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

darms21
Yak Posting Veteran

53 Posts
Posted - 05/01/2012 :  11:51:28  Show Profile  Reply with Quote
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

India
47173 Posts
Posted - 05/01/2012 :  11:57:53  Show Profile  Reply with Quote 

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


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/
Go to Top of Page

darms21
Yak Posting Veteran

53 Posts
Posted - 05/01/2012 :  12:01:49  Show Profile  Reply with Quote
Ahh...very good. I was not aware of the group by case ability.

Thanks!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 05/01/2012 :  12:06:59  Show Profile  Reply with Quote
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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000