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.
Author |
Topic |
srikanthk
Starting Member
2 Posts |
Posted - 2012-11-03 : 03:01:36
|
Hello every one,Myself srikanth, very new to sql server programming. i am using SQL Server 2005. I need help on grouping the data present in a column using SQL.Example:Time in Minutes1015162023243031I need to put above mentioned data as following formResult:Range (Num)<10 (0)10-20 (4)20-30 (3)>30 (1)Regards,srikanth |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-03 : 08:29:52
|
Should be able to do with something like this:SELECT CASE WHEN col1 < 10 THEN CAST('<10' AS VARCHAR(10)) WHEN col1 < 20 THEN '10-20' WHEN col1 < 30 THEN '20-30' ELSE '>30' END AS [Range], COUNT(*) AS [Num]FROM YourTableGROUP BY CASE WHEN col1 < 10 THEN CAST('<10' AS VARCHAR(10)) WHEN col1 < 20 THEN '10-20' WHEN col1 < 30 THEN '20-30' ELSE '>30' END |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-11-03 : 21:47:25
|
i would prefer putting ranges into a table of its own with boundary values so that it would be maintainable moving forward. This will give you ability to redefine ranges and add/remove ranges by means of DML statements to table rather than changing CASE WHEN inside procedure each time. The procedure will just have a join to this table on condition like...ON value BETWEEN startval AND endval.. ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
srikanthk
Starting Member
2 Posts |
Posted - 2012-11-04 : 23:31:37
|
@sunitabeck, Thank you for replying. code is working perfectly. @visakh16, Thank you for suggetion. |
|
|
|
|
|