SQL Server - find values in a range

Question

I have a requirement where i have to find the count of people in different age groups like:

0-5      12

6-10     35

11-15    26

16-20    57

20+      120

I am creating a stored procedure in SQL Server which will first create a temp table and store the age group like one column will have 0 and another will have 5 and so on. Then i'll loop through them and find the count between min and max range and will store the count with mim-max value in another temp table.

Is there any other easy and better way to achieve this output. Please advise.

Answer 1

Perhaps with SUM and CASE:

SELECT [0-5]   = SUM(CASE WHEN AGE >= 0 AND AGE <= 5 THEN 1 ELSE 0 END),
       [6-10]  = SUM(CASE WHEN AGE > 5 AND AGE <= 10 THEN 1 ELSE 0 END),
       [11-15] = SUM(CASE WHEN AGE > 10 AND AGE <= 15 THEN 1 ELSE 0 END),
       ....
FROM dbo.Persons

Demo

Or, with a range table you can use a LEFT OUTER JOIN, GROUP BY + COUNT:

SELECT r.[From], r.[To], [Count]=COUNT(p.Age)
FROM dbo.Ranges r
LEFT OUTER JOIN dbo.Persons p
   ON p.[Age] >= r.[From] AND  p.[Age] <= r.[To]
GROUP BY r.[From], r.[To]

Demo

Answer 2

Your plan is basically sound.

Having a range table with a min and max column is a good approach,

 AgeRange       MinAge      MaxAge
 5 and under    0           5
 6-10           6           10
 ...

but instead of "looping" you should try to think in terms of sets and joins - ie: joining to your data set on

on data.age between range.minage and range.maxage

and using group by and count to find your results

In general, it is a bad idea to store age in your dataset. It will never be accurate and will steadily decay.