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
 Can you group numeric data into intervals?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mrdatabase
Starting Member

United Kingdom
12 Posts
Posted - 04/28/2012 :  09:15:47  Show Profile  Reply with Quote
I have a query displaying just numeric data

e.g

5
10
3
4
5
0
2

However i would like to group this data into intervals say, how many of these results: equals 0, are between 1 and 3, are between 4 and 7, between 8 and 14, between 15 - 21, between 22 and infinity.


Is it possible to create these intervals and have a count of the which numbers fit into which spec?

I am using Microsoft Access to write my SQL

Thanks

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 04/28/2012 :  11:02:56  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Yes. Just divide with the interval you are investigating.
Or have an auxiliary table with two column; FromValue and ToValue, and join against this table.

N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

robvolk
Most Valuable Yak

USA
15557 Posts
Posted - 04/28/2012 :  14:09:14  Show Profile  Visit robvolk's Homepage  Reply with Quote
Another method:
SELECT Interval, Count(*) FROM (SELECT CASE WHEN num<=0 THEN '0'
WHEN num<=3 THEN '1-3'
WHEN num<=7 THEN '4-7'
WHEN num<=14 THEN '8-14'
WHEN num<=21 THEN '15-21'
ELSE '22-infinity' END AS Interval
FROM myTable) A
GROUP BY Interval
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 04/28/2012 :  19:21:54  Show Profile  Reply with Quote
I am using Microsoft Access to write my SQL
then you should have posted this access forums

Though most sql queries work in both access and t-sql there are few variations.Also solutions given here might be mostly t-sql based

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

robvolk
Most Valuable Yak

USA
15557 Posts
Posted - 04/28/2012 :  22:03:22  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
I am using Microsoft Access to write my SQL
Someday...mark my words...I will actually READ the question before I post an answer.

Here's an Access version:
SELECT Switch(num<=0,"0",num<=3,"1-3",num<=7,"4-7",num<=14,"8-14",num<=21,"15-21",num>=22,"22-infinity") AS Interval,
Count(*)
FROM myTable
GROUP BY Switch(num<=0,"0",num<=3,"1-3",num<=7,"4-7",num<=14,"8-14",num<=21,"15-21",num>=22,"22-infinity")
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.06 seconds. Powered By: Snitz Forums 2000