CODEWITHSUNDEEP
sql
Brian Coolidge
Brian Coolidge

Reputation: 4649

Retrieving and Count no. of rows in birthdate to Age with multiple WHERE clause in a single query?

Well, lets say I have this list of birthdates

enter image description here

And I want to count the data with corresponding ages like, 

ages 10 - 15
ages 16 - 20
ages 21 - 30
and so on...

Well, I found this piece of code from this site

SELECT * 
FROM your_table 
WHERE YEAR(CURDATE())-YEAR(BirthDate) BETWEEN 10 AND 20;

Which actually helps but, how would I apply it by using multiple where clause?

Guessing Code Representation

"SELECT COUNT(birthday) a IN (SELECT birthday FROM WHERE YEAR(CURDATE())-YEAR(BirthDate) BETWEEN 10 AND 15), COUNT(birthday) b IN (SELECT birthday FROM WHERE YEAR(CURDATE())-YEAR(BirthDate) BETWEEN 16 AND 20) FROM table"

then if I echo it, it would result to

Total result of age 10 - 15 : **25**

Total result of age 16 - 20 : **12**

Is there anything like that? Any help would be appreciated.

Upvotes: 0

Views: 141

Answers (1)

Matt MacLean
Matt MacLean

Reputation: 19648

This works in MySQL:

SELECT AGE_RANGE, COUNT(*) FROM (
    SELECT CASE
        WHEN YearsOld BETWEEN 0 AND 5 THEN '0-5'
        WHEN YearsOld BETWEEN 6 AND 10 THEN '6-10'
        WHEN YearsOld BETWEEN 11 AND 15 THEN '11-15'
        WHEN YearsOld BETWEEN 16 AND 20 THEN '16-20'
        WHEN YearsOld BETWEEN 21 AND 30 THEN '21-30'
        WHEN YearsOld BETWEEN 31 AND 40 THEN '31-40'
        WHEN YearsOld > 40 THEN '40+'
        END AS 'AGE_RANGE'
        FROM (
            SELECT YEAR(CURDATE())-YEAR(DATE(birthdate)) 'YearsOld' 
            FROM MyTable
        ) B
) A
GROUP BY AGE_RANGE

Upvotes: 2

Related Questions