Group people by birthday in 10 year intervals

[davurs]

In a members db in MariaDB/MySQL, I have a DATE field (YYYY-MM-DD) to hold the birthday of my members. I can then do something like

select count(*) from people_name where YEAR(curdate()) - YEAR(birthday) between 15 and 30;

What I would be interested in, is a query to perform this for several intervals in one shot, say 20-30, 30-40, 40-50, etc.

Any advice?

[coopster]

Welcome to WebmasterWorld, davurs.

How about using a UNION?

[davurs]

Thanks for welcome and advice,

yes UNION is one way to achieve the result but then I get another problem to solve:

select count(*) as grouptotal from people_name 
-> where YEAR(curdate()) - YEAR(birthday) between 20 and 30 
-> union select count(*) as grouptotal from people_name 
-> where YEAR(curdate()) - YEAR(birthday) between 31 and 40 
-> union select count(*) as grouptotal from people_name 
-> where YEAR(curdate()) - YEAR(birthday) between 41 and 50

yields me a single column like

+------------+
| grouptotal |
+------------+
|     0 |
|     5 |
|     6 |
+------------+

which is OK but I'll then still need to label the rows in PHP by hand. Is there a way to add into such a query maybe a temporary column with appropriate row labels, like agegroup

+----------+------------+
| agegroup | grouptotal |
+----------+------------+
|  20-30 |     0 |
|  31-40 |      5 |
|  41-50 |     6 |
+----------+------------+

Thanks in advance :)

[coopster]

Don't forget you can always use literal values as columns:

select '20-30' AS agegroup, count(*) as grouptotal from people_name 
-> where YEAR(curdate()) - YEAR(birthday) between 20 and 30 
-> union select '31-40' AS agegroup, count(*) as grouptotal from people_name 
-> where YEAR(curdate()) - YEAR(birthday) between 31 and 40 
-> union select '41-50' AS agegroup, count(*) as grouptotal from people_name 
-> where YEAR(curdate()) - YEAR(birthday) between 41 and 50

[davurs]

I've been away from SQL way too long - literal values never even crossed my mind. It worked like a charm - thanks :)