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 |
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2013-01-11 : 09:10:25
|
now sure how to word this for searching, so hopefully a quick post will help. if i have a dataset of a bunch of number, is there any way to group those numbers in hundreds?i.e. i have a dataset of:1235532213147233400i would want to generate a query that tells me:100-199 i have 2 records200-299 i have 2 records400-499 i have 15500-5599 i have 1but w/o having to for example write a case and have to spell out the ranges. any help would be appreciated. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 09:15:39
|
[code]SELECT Descr,COALESCE(Cnt,0) AS CntFROM (SELECT '100 - 199' AS Descr,100 As StartVal,199 AS EndVal UNION ALL SELECT '200 - 299',200,299 UNION ALL ... )tOUTER APPLY (SELECT COUNT(*) AS Cnt FROM Table WHERE Val BETWEEN t.StartVal AND t.EndVal )u[/code]if you want ranges to persist create a table with those ranges and descriptions------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2013-01-11 : 09:20:09
|
so in this case though, you are still telling it each range. i was looking for something that would be able to just group by the 100 incraments auto-magically. nothig you know of? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 09:22:14
|
quote: Originally posted by albertkohl so in this case though, you are still telling it each range. i was looking for something that would be able to just group by the 100 incraments auto-magically. nothig you know of?
for that you can do thisSELECT Number/100,COUNT(*)FROM TableGROUP BY Number/100 but those descriptions still have to be generated manually------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2013-01-11 : 09:59:57
|
understood. thanks! :) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 10:40:41
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|