April 14, 2014 at 8:47 am
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).
April 14, 2014 at 8:55 am
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
April 14, 2014 at 9:10 am
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
April 14, 2014 at 9:35 am
Thanks Jason. This works well, my bandings are evenly distributed. This is exactly what I expected.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply