CODEWITHSUNDEEP
phpmysqlfunctiongroup-by
kesm0
kesm0

Reputation: 875

Age Groups stats with PHP and MYSQL

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 ?

Upvotes: 4

Views: 1448

Answers (4)

Joel Harkes
Joel Harkes

Reputation: 11661

If you want pure sql:

SELECT COUNT(*)    
FROM [table_name]
WHERE birth_date < DATE_ADD(NOW(),INTERVAL 13 YEAR)


WHERE birth_date >= DATE_ADD(NOW(),INTERVAL 13 YEAR)
AND birth_date < DATE_ADD(NOW(),INTERVAL 15 YEAR)

--etc

else I would suggest using php to create the dates.

You could union all the queries together and create and sql view eg:

CREATE VIEW statistics AS
SELECT "0-13" as age ,COUNT(*) as total
FROM table_name
WHERE birth_date < DATE_ADD(NOW(),INTERVAL 13 YEAR)
UNION
SELECT "13-15" as age ,COUNT(*) as total
FROM table_name
WHERE birth_date >= DATE_ADD(NOW(),INTERVAL 13 YEAR)
    AND birth_date < DATE_ADD(NOW(),INTERVAL 15 YEAR)
UNION
SELECT "15-20" as age ,COUNT(*) as total
FROM table_name
WHERE birth_date >= DATE_ADD(NOW(),INTERVAL 15 YEAR)
    AND birth_date < DATE_ADD(NOW(),INTERVAL 20 YEAR)
UNION
SELECT "20-23" as age ,COUNT(*) as total
FROM table_name
WHERE birth_date >= DATE_ADD(NOW(),INTERVAL 20 YEAR)
    AND birth_date < DATE_ADD(NOW(),INTERVAL 23 YEAR)
UNION
SELECT "23+" as age ,COUNT(*) as total
FROM table_name
WHERE birth_date >= DATE_ADD(NOW(),INTERVAL 23 YEAR)

Then you can just query:

SELECT * from statistics

Upvotes: 3

BlitZ
BlitZ

Reputation: 12168

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.

Upvotes: 5

juergen d
juergen d

Reputation: 204766

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

Upvotes: 4

Glitch Desire
Glitch Desire

Reputation: 15023

Return datetime as a timestamp, compare with now.offset(years=??), categorize in arrays then count records.

Upvotes: 0

Related Questions