Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate number of records for each range of values Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, April 14, 2014 8:47 AM
 SSC-Addicted Group: General Forum Members Last Login: 2 days ago @ 4:07 PM Points: 421, Visits: 1,309
 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.t1goCREATE TABLE t1(c1 int)goSET NOCOUNT ONINSERT INTO dbo.t1SELECT 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 ALLSELECT range_begin = 11, range_end = 20, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 11 AND 20 UNION ALLSELECT range_begin = 21, range_end = 30, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 21 AND 30 UNION ALLSELECT range_begin = 31, range_end = 40, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 31 AND 40 UNION ALLSELECT range_begin = 41, range_end = 50, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 41 AND 50 UNION ALLSELECT range_begin = 51, range_end = 60, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 51 AND 60 UNION ALLSELECT range_begin = 61, range_end = 70, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 61 AND 70 UNION ALLSELECT range_begin = 71, range_end = 80, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 71 AND 80 UNION ALLSELECT range_begin = 81, range_end = 90, recs = COUNT(*) FROM dbo.t1 WHERE c1 BETWEEN 81 AND 90 UNION ALLSELECT 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 Group: General Forum Members Last Login: Yesterday @ 9:59 AM Points: 5,227, Visits: 9,437
 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
 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 #SampleVALUES (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 #SampleGroup 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 Group: General Forum Members Last Login: 2 days ago @ 4:07 PM Points: 421, Visits: 1,309
 Thanks Jason. This works well, my bandings are evenly distributed. This is exactly what I expected.
Post #1561527

 Permissions