I want to display users stats on my website, returning the percentage of age groups like :
-13 years : $percent %
13-15 years : $percent %
15-20 years : $percent %
23+ : $percent %
In my mysql table i have a column birth_date returning datatime (yyyy-mm-dd).
Did you have hints or idea to do that ?
Pure SQL:
SELECT
`group`,
COUNT(*) as `count`
FROM
`user`
INNER JOIN (
SELECT
0 as `start`, 12 as `end`, '0-12' as `group`
UNION ALL
SELECT
13, 14, '13-14'
UNION ALL
SELECT
15, 19, '15-19'
UNION ALL
SELECT
20, 150, '20+'
) `sub`
ON TIMESTAMPDIFF(YEAR, `birth_date`, NOW()) BETWEEN `start` AND `end`
GROUP BY `group` WITH ROLLUP;
Anything else might be calculated via PHP
.
select case when year(curdate() - birth_date) < 13
then '< 13 years'
when year(curdate() - birth_date) between 13 and 14
then '13 - 14 years'
when year(curdate() - birth_date) between 15 and 20
then '15 - 20 years'
when year(curdate() - birth_date) >= 23
then '+23 years'
end as `description`,
(select count(*) from your_table) / count(*)
from your_table
group by case when year(curdate() - birth_date) < 13 then 1
when year(curdate() - birth_date) between 13 and 14 then 2
when year(curdate() - birth_date) between 15 and 20 then 3
when year(curdate() - birth_date) >= 23 then 4
end
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