CODEWITHSUNDEEP
mysqlsql
three3
three3

Reputation: 2846

MySQL query to Count Number of Users in Age Group

I am calculating the age of my users in MySQL and I am running into a little problem. I am able to successfully calculate the age of each user, however, when I try to count the number of users who are part of each age group, that is when I run into trouble. Here is my query:

  SELECT COUNT(user_id) AS "Number of Users", 
           YEAR(CURDATE()) - 
           YEAR(STR_TO_DATE(birth_date, '%m/%d/%Y')) - 
           (RIGHT(CURDATE(), 5) < RIGHT(STR_TO_DATE(birth_date, '%m/%d/%Y'), 5)) 
             AS Age
    FROM user 
GROUP BY Age

I feel like I am close, it just is not working for me. How would I count the number of users in each age group?

Upvotes: 1

Views: 1723

Answers (1)

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

You need a subquery to access calculated column aliased Age in Group By clause

SELECT Age,COUNT(user_id) AS "Number of Users"

FROM
(
    SELECT userid,
           YEAR(CURDATE()) - 
           YEAR(STR_TO_DATE(birth_date, '%m/%d/%Y')) - 
           (RIGHT(CURDATE(), 5) < RIGHT(STR_TO_DATE(birth_date, '%m/%d/%Y'), 5)) 
             AS Age
    FROM user
) as Z 
GROUP BY Age

Upvotes: 2

Related Questions