From a birthday date list to an array of the total user from each individual age

Question

I will like to understand the most efficient way to achieve this:

I have a MySQL table with my users info, including birthday (YYYY/MM/DD). My objective is to retrieve an array (php) with the total of user for each individual age from 10 to 60 years old. I can just query all my users, pass the birthday to a birthday to age php function I wrote and then populate an array with the totals. But I wonder if there's a way to build an sql query that does this job for me?

Searching `mysql birthday` on SO gets me a lot of hits, are they not helpful? http://stackoverflow.com/search?q=mysql+birthday — Pekka, Jan 19 '13 at 23:15
This should help: [Calculate Age in MySQL (InnoDb)](http://stackoverflow.com/q/5773405) — Pekka, Jan 19 '13 at 23:20

Adam Hopkinson · Accepted Answer · 2013-01-20T00:16:54.787

2

You should be able to group the rows:

SELECT
    FLOOR(DATEDIFF(NOW(), `birthday`)/365) AS 'Age',
    COUNT(*)
FROM `users`
WHERE FLOOR(DATEDIFF(NOW(), `birthday`)/365) BETWEEN 10 AND 60
AND `id` IN (1, 3, 5, 12, 29)
GROUP BY FLOOR(DATEDIFF(NOW(), `birthday`)/365)
ORDER BY FLOOR(DATEDIFF(NOW(), `birthday`)/365) ASC

The result won't contain ages that have no users, but I'm not sure why you'd need that.

Updates - Added an id filter - Fixed the date calculation (oops!) - Named the new column

edited Jan 20 '13 at 00:16

answered Jan 19 '13 at 23:26

Adam Hopkinson

28,281
7
65
99

This could do the trick, but i wonder, what if instead of retrieving all the users i retrieve just the ones with specific Ids, like passing an array of ids on the query, can you please show me how to modify your query in order to achieve this? – DomingoSL Jan 19 '13 at 23:30
I've added that to my answer – Adam Hopkinson Jan 19 '13 at 23:32
Why don't you alias ``YEAR(NOW() - `birthday`)``? – Joseph Silber Jan 19 '13 at 23:44
The operation year(now() - birthday) is giving me an strange result: http://postimage.org/image/7k092fvtn/ – DomingoSL Jan 19 '13 at 23:49
@DomingoSL it's because the column isn't named, so the name is taken from the calculation - see my updated answer. – Adam Hopkinson Jan 19 '13 at 23:57
the error is not the label, is that 2013 value, what it means? i dont have users with 2013 year old – DomingoSL Jan 20 '13 at 00:01
Oops, sorry - I've fixed a calculation error (didn't have mysql to hand earlier) – Adam Hopkinson Jan 20 '13 at 00:17

Ian Brindley · Answer 2 · 2013-01-19T23:28:29.067

0

SELECT * FROM tableName 
         WHERE dateOfBirth >= (CURRENT_DATE - INTERVAL 60 YEAR)   
         AND dateOfBirth <= ( CURRENT_DATE - INTERVAL 10 YEAR )

Hopefully I don't get slammed by the database experts on this one...

edited Jan 19 '13 at 23:28

answered Jan 19 '13 at 23:21

Ian Brindley

2,197
1
19
28

That would get you all the users between 10-60 years old. The OP wants a count of users per year (of age) – Adam Hopkinson Jan 19 '13 at 23:32
Good code though - I always get really stuck with MySQL intervals – Adam Hopkinson Jan 19 '13 at 23:35

score -1 · Answer 3 · answered Jan 19 '13 at 23:19

-1

You will need to use MySql's DATEDIFF().

SELECT USER, DATEOFBIRTH FROM USERTABLE WHERE DATEDIFF(DATEOFBIRTH,NOW()) < 60;

answered Jan 19 '13 at 23:19

WhoaItsAFactorial

3,538
4
28
45

From a birthday date list to an array of the total user from each individual age

3 Answers3