I use the following query quite a lot but am pretty sure there must be a more efficient way to do it.
Basically, I am counting the decades people were born in, from within a user table:
select count(*) as howmany, yyyy from bday where (((yyyy > '1949')
AND (yyyy < '1961')) AND (user_id = '63')) UNION
select count(*) as howmany, yyyy from bday where (((yyyy > '1959')
AND (yyyy < '1971')) AND (user_id = '63')) UNION
select count(*) as howmany, yyyy from bday where (((yyyy > '1969')
AND (yyyy < '1981')) AND (user_id = '63')) UNION
select count(*) as howmany, yyyy from bday where (((yyyy > '1979')
AND (yyyy < '1991')) AND (user_id = '63')) UNION
select count(*) as howmany, yyyy from bday where (((yyyy > '1989')
AND (yyyy < '2001')) AND (user_id = '63'))
You can calculate the decade and use it to group the users:
SELECT
COUNT(id) AS howmany,
CEIL((`yyyy`+1)/10) AS decade,
yyyy
FROM `bday`
GROUP BY decade
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With