Find count, by age, of everyone in database

Question

I have a MySQL database with thousands of member records. Each one has a birthdate which is a simple string like "2005-11-01" (yyyy-mm-dd)

I want to query the database and get an idea of the AGE of my members. I'd love to see how many members I have at each age in the db. So I'd love some output like:

age         count
---         -----
18          619
19          1321
20          15112
etc.

How can I do this? (I'd love to do it all in MySQL but we can add some PHP, Perl or Python if it's easier)

For those who have voted this down or voted to close it - why the downvotes? This seems like exactly a perfect stackoverflow question... clear question, one clear answer, not subjective etc... ?? I'd love to understand why this is getting the hate. — Eric, May 20 '19 at 09:05

score 2 · Answer 1 · answered May 19 '19 at 15:24

2

You clould use

SELECT TIMESTAMPDIFF(YEAR, '2005-11-01', CURDATE()) AS age , count(*) 
FROM my_table 
group by age ;

SELECT TIMESTAMPDIFF(YEAR, dob, CURDATE()) AS age , count(*) 
FROM my_table 
group by age ;

answered May 19 '19 at 15:24

ScaisEdge

129,293
10
87
97

Phil Coulson · Accepted Answer · 2019-05-20T04:11:49.547

1

If you wish to take care of leap years as well, try something like this:

SELECT 
YEAR(CURDATE())-YEAR(DOB) - (DATE_FORMAT(CURDATE(),'%M%D') < DATE_FORMAT(DOB,'%M%D')) `AGE`,
COUNT(*) `COUNT`
FROM YOURTABLE
GROUP BY AGE;

edited May 20 '19 at 04:11

answered May 20 '19 at 04:05

Phil Coulson

3,734
2
9
20

Find count, by age, of everyone in database

2 Answers2