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
 General SQL Server Forums
 New to SQL Server Programming
 grouping question

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:
123
5532
213
147
233
400

i would want to generate a query that tells me:
100-199 i have 2 records
200-299 i have 2 records
400-499 i have 1
5500-5599 i have 1

but 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 Cnt
FROM (SELECT '100 - 199' AS Descr,100 As StartVal,199 AS EndVal
UNION ALL
SELECT '200 - 299',200,299
UNION ALL
...
)t
OUTER 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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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

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 this

SELECT Number/100,COUNT(*)
FROM Table
GROUP BY Number/100


but those descriptions still have to be generated manually

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

Go to Top of Page

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2013-01-11 : 09:59:57
understood. thanks! :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-11 : 10:40:41
welcome

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

Go to Top of Page
   

- Advertisement -