I have a table called Person that contain a field called PersonAge. I need to group the ages by age bands ie '12 and under', '13-17', '18-25', '25 and over' and return this resultset using a stored procedure.
Ideally I need to get returned 2 fields , 'Age Band', 'Total' like so
Age band Total
12 and under 5
13 - 17 8
18 - 25 7
25 and over 10
Create a table containing your bands:
CREATE TABLE agebands ( id INT NOT NULL PRIMARY KEY, lower_bound INT NOT NULL, upper_bound INT NOT NULL ) CREATE INDEX IDX_agebands_bounds ON (lower_bound, upper_bound)
Then populate it with your data:
INSERT INTO agebands VALUES (1, 0, 12) INSERT INTO agebands VALUES (2, 13, 17) INSERT INTO agebands VALUES (3, 18, 24) INSERT INTO agebands VALUES (4, 25, 199)
Then join with it:
SELECT lower_bound, upper_bound, COUNT(*) AS number_of_people FROM persons INNER JOIN agebands ON person_age BETWEEN lower_bound AND upper_bound GROUP BY lower_bound, upper_bound ORDER BY lower_bound
This allows for flexibility in adjusting the bands. Of course, the other answer here using UNION is usable too, which is more appropriate if you can/won't add another table to your database.