Reputation: 775
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.
Upvotes: 3
Views: 3688
Reputation: 460360
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
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]
Upvotes: 6
Reputation: 51514
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.
Upvotes: 2