| Group people by birthday in 10 year intervals
|
davurs
msg:4266361 | 10:54 am on Feb 13, 2011 (gmt 0) |
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
msg:4266816 | 2:51 pm on Feb 14, 2011 (gmt 0) |
Welcome to WebmasterWorld, davurs.
How about using a UNION?
|
davurs
msg:4267017 | 9:16 pm on Feb 14, 2011 (gmt 0) |
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
msg:4267026 | 9:41 pm on Feb 14, 2011 (gmt 0) |
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
msg:4267051 | 10:24 pm on Feb 14, 2011 (gmt 0) |
I've been away from SQL way too long - literal values never even crossed my mind. It worked like a charm - thanks :)
|
|
|
