SQL Creating a stored proc for returning age bands

Question

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

Answer 1

A simple UNION should suffice.

SELECT [Ageband] = '12 and under', COUNT(*)
FROM dbo.Person
WHERE PersonAge <= 12
UNION ALL SELECT '13-17', COUNT(*)
FROM dbo.Person
WHERE PersonAge BETWEEN 13 AND 17
UNION ALL SELECT '18-25', COUNT(*)
FROM dbo.Person
WHERE PersonAge BETWEEN 18 AND 25
UNION ALL SELECT '26 and over', COUNT(*)
FROM dbo.Person
WHERE PersonAge >= 26

Answer 2

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.

Answer 3

The following should give:

select count(*), person_age
(select (case age when between 0 and 12 then '12 and under'
               when between 13 and 17 then '13-17'
               when 18 and 25 then '18-15'
               else 'Above 25'
      end) person_age
from person)
group by person_age

Answer 4

declare @tempT table(age int)

declare @temp2 table(age2 varchar(15))

insert into @tempT(age) values(1) insert into @tempT(age) values(2) insert into @tempT(age) values(3) insert into @tempT(age) values(4) insert into @tempT(age) values(5) insert into @tempT(age) values(6) insert into @tempT(age) values(7) insert into @tempT(age) values(8) insert into @tempT(age) values(9) insert into @tempT(age) values(10) insert into @tempT(age) values(11)

insert into @Temp2 select case
when age < 3 then '<3' when age >= 3 and age < 5 then '>= 3 and < 5' when age >= 5 then '>=5' end from @tempT

select count(*), age2 from @Temp2 GROUP BY age2

Answer 5

In SQL you cannot group by a column alias. You'd need to repeat the case statement like this.

select count(*), 
case  
    when aged between 0 and 12 then '12 and under'
    when aged between 13 and 17 then '13-17'
    when aged between 18 and 25 then '18-15'
    else 'Above 25' end
from person
group by 
case  
    when aged between 0 and 12 then '12 and under'
    when aged between 13 and 17 then '13-17'
    when aged between 18 and 25 then '18-15'
    else 'Above 25'
end