Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculate number of records for each range of values Expand / Collapse
Author
Message
Posted Monday, April 14, 2014 8:47 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 439, Visits: 1,389
I have a table where I need to calculate number of records for each range of values. My table is much larger, but here I bring very simplified example.

I have a table with an integer column which I populate with random values from 1 to 100:


IF EXISTS(SELECT OBJECT_ID('t1','u'))
DROP TABLE dbo.t1
go

CREATE TABLE t1
(c1 int)
go

SET NOCOUNT ON
INSERT INTO dbo.t1
SELECT FLOOR(RAND()*100)
go 100


I need to find number of records for range between 1 to 10, another one 11-20, and so on...

I wrote dynamic script in a loop which produces this code:


SELECT range_begin = 1, range_end = 10, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 1 AND 10 UNION ALL
SELECT range_begin = 11, range_end = 20, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 11 AND 20 UNION ALL
SELECT range_begin = 21, range_end = 30, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 21 AND 30 UNION ALL
SELECT range_begin = 31, range_end = 40, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 31 AND 40 UNION ALL
SELECT range_begin = 41, range_end = 50, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 41 AND 50 UNION ALL
SELECT range_begin = 51, range_end = 60, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 51 AND 60 UNION ALL
SELECT range_begin = 61, range_end = 70, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 61 AND 70 UNION ALL
SELECT range_begin = 71, range_end = 80, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 71 AND 80 UNION ALL
SELECT range_begin = 81, range_end = 90, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 81 AND 90 UNION ALL
SELECT range_begin = 91, range_end = 100, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 91 AND 100



After its execution I get a result set somethinbg like that:

range_begin   range_end        recs
----------- ----------- -----------
1 10 10
11 20 13
21 30 8
31 40 18
41 50 9
51 60 5
61 70 9
71 80 11
81 90 9
91 100 7


But my goal is to create a set-based solution, and this is what I am asking forum for help.
First, the code will be much neater, and it will not have mutiple table scans (again, in my actual case, the table is much larger).

Post #1561503
Posted Monday, April 14, 2014 8:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:07 AM
Points: 5,339, Visits: 9,794
Just create a table (either permanent or on-the-fly) with the range limits in, and join it to your data to get the row counts.

John
Post #1561505
Posted Monday, April 14, 2014 9:10 AM This worked for the OP Answer marked as solution
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
This may be what you are looking for

/*
SAMPLE DATA POPULATION
*/
CREATE TABLE #Sample
(
Id INT
)

Insert into #Sample
VALUES
(1),(12),(13),(23),(22),(22),(9)

/*
Get the ranges
*/

SELECT
(10*(Id/10))+1 AS RangeStart
,(10*(Id/10))+10 AS RangeEnd
,COUNT(*) AS Recs
FROM
#Sample
Group By
(Id/10)

This really only works if the bandings are evenly distributed, if you have uneven bands setting up a banding table may be the optimal solution.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1561516
Posted Monday, April 14, 2014 9:35 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:40 AM
Points: 439, Visits: 1,389
Thanks Jason. This works well, my bandings are evenly distributed. This is exactly what I expected.
Post #1561527
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse