CODEWITHSUNDEEP
mysqldate
Dil.
Dil.

Reputation: 2076

How to use multiple group by conditions in mysql

I have a customer_master table. In that table I have two columns called customer_id and date_of_birth.
what I want is get count of customers group by their age ranger. Something like this.

enter image description here

So far this is the only query I could try.

select COUNT(customer_id) AS count FROM customer_master WHERE (DATEDIFF( CURDATE(),date_of_birth) / 365.25)<40
Please help me out with this. Thank you.


With everyone's help I found a perfect answer than you all. 

SELECT CASE

 WHEN (DATEDIFF( CURDATE(),STR_TO_DATE(date_of_birth, '%Y-%m-%d')) / 365) <= 20 THEN 'Below 20'
            WHEN(DATEDIFF( CURDATE(),STR_TO_DATE(date_of_birth, '%Y-%m-%d')) / 365) <= 30 THEN 'Below 30'
            WHEN (DATEDIFF( CURDATE(),STR_TO_DATE(date_of_birth, '%Y-%m-%d')) / 365) <= 40 THEN 'Below 40'
            WHEN (DATEDIFF( CURDATE(),STR_TO_DATE(date_of_birth, '%Y-%m-%d')) / 365) <= 50 THEN 'Below 50'
    ELSE 'Over 50'
   END as age_group, 
   COUNT(customer_id) 

 FROM customer_master 
 GROUP BY age_group;

Upvotes: 0

Views: 54

Answers (3)

tim_schaaf
tim_schaaf

Reputation: 259

One solution would be to use cascading values with CASE within a subquery:

select age_group, count(customer_id) as 'count' from
    (select customer_id,
       year(curdate())-year(date_of_birth) as 'age',
       case when (year(curdate())-year(date_of_birth)) < 20, "Below 20"
       when (year(curdate())-year(date_of_birth)) < 30, "Between 20 and 29"
       when (year(curdate())-year(date_of_birth)) < 40, "Between 30 and 39"
       else "40 or Greater" end as 'age_group'
    FROM customer_master) x
group by age_group

Upvotes: 1

Michał Chatłas
Michał Chatłas

Reputation: 98

You can use the CASE operator.

SELECT CASE
         WHEN (DATEDIFF( CURDATE(),date_of_birth) / 365.25) < 40 THEN 'Below 40'
         ELSE 'Over 40'
       END as age_group, 
       COUNT(customer_id) 
FROM customer_master 
GROUP BY age_group;

Sorry for poor formatting, it is my first answer

Upvotes: 4

Gustek
Gustek

Reputation: 3760

SELECT 
  (year(curdate())-year(date_of_birth)) div 20 as age_group,
  COUNT(customer_id)
FROM 
  customer_master
GROUP BY age_group

Something like this will give you number of customers in every 20. If you want different size of group just change the number you divide by.

That is assuming you want each group to be same size e.g

1 - 20
21 - 40
41 - 60
...

If you want different sizes go with CASE solution as other have suggested.

Upvotes: 0

Related Questions