CODEWITHSUNDEEP
mysqlsqldatabase
user1544337
user1544337

Reputation:

Selecting age groups using SQL

This is a follow-up question on Get age from the birthday field with type date using SQL. I have a date field in a MySQL database for the birthday of a user and get the age using this query:

SELECT 
    ROUND(DATEDIFF(
        Cast((SELECT NOW()) as Date),
        Cast(birthday as Date)
    ) / 365, 0) as age
FROM member

Now, I need to select the number of people in different age groups. For example, I need to know how many people are in the age group 13-17, 18-21, 22-25, 26-35, 36-50, 51-MAX.

Is that possible using MySQL?

I have thought of UNIONs, like this:

SELECT 
    ROUND(DATEDIFF(
        Cast((SELECT NOW()) as Date),
        Cast(birthday as Date)
    ) / 365, 0) as age,
    1 as agegroup
FROM member WHERE age >=13 AND age <=17

UNION

SELECT 
    ROUND(DATEDIFF(
        Cast((SELECT NOW()) as Date),
        Cast(birthday as Date)
    ) / 365, 0) as age
    2 as agegroup
FROM member WHERE age >=18 AND age <=21

But that would be long and ugly. There must be a better way!

Upvotes: 1

Views: 6571

Answers (3)

Kickstart
Kickstart

Reputation: 21513

Another possible solution:-

SELECT  AgeRange.MinAge, AgeRange.MaxAge, COUNT(*)
FROM    
(
    SELECT 13 AS MinAge, 17 AS MaxAge
    UNION SELECT 18, 21
    UNION SELECT 22, 25
    UNION SELECT 26, 35
    UNION SELECT 36, 50
    UNION SELECT 51, 9999
) AgeRange
INNER JOIN YourTable
ON ROUND(DATEDIFF(CAST(NOW() as DATE), CAST(birthday as DATE)) / 365, 0) BETWEEN AgeRange.MinAge AND AgeRange.MaxAge 
GROUP BY AgeRange.MinAge, AgeRange.MaxAge

Possibly easier to expand if needs be, or to move to using date ranges from a table (so the resulting report could be updated by users easily if required).

Upvotes: 2

nurdglaw
nurdglaw

Reputation: 2127

If you had the age as a column in a table you would do it like this:

SELECT
    SUM(CASE WHEN age < 10 THEN 1 ELSE 0 END) AS under10,
    SUM(CASE WHEN 10<age AND age <19 THEN 1 ELSE 0 END) AS age10to19,
    .
    .
    .
FROM table

There are likely to be minor changes because age isn't in its own column or if you want extra or different ranges. I'm sure you can work them out yourself!

Upvotes: 0

Andomar
Andomar

Reputation: 238086

select  AgeGroup
,       count(*)
from    (
        select  case
                when  age between 13 and 17 then 1
                when  age between 18 and 21 then 2
                ...
                end as AgeGroup
        from    (
                SELECT  ROUND(DATEDIFF(Cast(NOW() as Date),
                            Cast(birthday as Date)) / 365, 0) as age
                FROM    YourTable
                ) as SubQueryAlias
        ) as SubQueryAlias2
group by
        AgeGroup

Upvotes: 3

Related Questions