homepage Welcome to WebmasterWorld Guest from 54.196.159.11
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
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 :)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved