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
 General SQL Server Forums
 New to SQL Server Programming
 grouping question		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

albertkohl
Aged Yak Warrior

USA
723 Posts
Posted - 01/11/2013 :  09:10:25  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

India
47173 Posts
Posted - 01/11/2013 :  09:15:39  Show Profile  Reply with Quote 

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


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

USA
723 Posts
Posted - 01/11/2013 :  09:20:09  Show Profile  Visit albertkohl's Homepage  Reply with Quote
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

India
47173 Posts
Posted - 01/11/2013 :  09:22:14  Show Profile  Reply with Quote
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

USA
723 Posts
Posted - 01/11/2013 :  09:59:57  Show Profile  Visit albertkohl's Homepage  Reply with Quote
understood. thanks! :)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 01/11/2013 :  10:40:41  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
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.3 seconds. Powered By: Snitz Forums 2000