mysql different_MySQL row count with different age ranges

可以将文章内容翻译成中文,广告屏蔽插件会导致该功能失效:

问题:

I have a MySQL table as follows.

emp_no emp_name dob gender

1 A 1978-10-10 Male

2 B 1985-02-20 Female

3 C 1982-04-16 Female

4 D 1980-06-27 Male

Then I need to get the following result.

age_group No of Male No of Female Total

<25 1 0 1

25-35 0 1 1

35-50 0 1 1

>50 1 0 1

I used the following query to get this output.

SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS age_group,

COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) <25, 1, 0 ) ) AS ag_C,

COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) BETWEEN 25 AND 35 , 1, 0 ) ) AS ag_B,

COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) BETWEEN 35 AND 50 , 1, 0 ) ) AS ag_C,

COUNT( IF( TIMESTAMPDIFF(YEAR, dob, NOW()) >50, 1, 0 ) ) AS ag_D

FROM emp GROUP BY age_group

But I can not get the desired out put. Can anyone help me on this issue ?

回答1:

try this

SELECT Case

when TIMESTAMPDIFF(YEAR, dob, NOW()) < 25 then '<25'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 25 and 35 then '25-35'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 36 and 50 then '36-50'

else '>50' END AS age_group,

sum(if(gender='Male', 1, 0)) as Male,

sum(if(gender='Female', 1, 0)) as Female,

COUNT(1) as total FROM emp

GROUP BY Case

when TIMESTAMPDIFF(YEAR, dob, NOW()) < 25 then '<25'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 25 and 35 then '25-35'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 36 and 50 then '36-50'

else '>50' END;

回答2:

Try below:

SELECT Case

when TIMESTAMPDIFF(YEAR, dob, NOW()) < 25 then '<25'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 25 and 35 then '25-35'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 36 and 50 then '36-50'

else '>50' END AS age_group,

sum(case when gender='Male' then 1 end) as Male,

sum(case when gender='Female' then 1 end) as Female,

sum(case when gender in ('Male','Female') then 1 end) as total,

GROUP BY Case

when TIMESTAMPDIFF(YEAR, dob, NOW()) < 25 then '<25'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 25 and 35 then '25-35'

when TIMESTAMPDIFF(YEAR, dob, NOW()) between 36 and 50 then '36-50'

else '>50' END

回答3:

You could try this:

select emp_no,

emp_name,

dob,

gender,

case when age < 25 then 1 else 0 end `<25`,

case when age >= 25 and age < 35 then 1 else 0 end `25-35`,

case when age >= 35 and age < 50 then 1 else 0 end `35-50`,

case when age >= 50 then 1 else 0 end `>=50`

from (

select emp_no,

emp_name,

dob,

gender,

year(now()) - year(dob) - (date_format(now(), '%m%d') < date_format(dob, '%m%d'))

age

from tbl

) a

回答4:

use case when and sub-query

select

case when age_group < 25 then '<25',

case when age_group >= 25 and age_group < 35 then '25-35',

case when age_group >= 35 and age_group < 50 then '35-50',

case when age_group >= 50 then '>=50' else null end as age_grp,

sum(case when gender='Male' then 1 end) as Male,

sum(case when gender='Female' then 1 end) as Female,

sum(case when gender in ('Male','Female') then 1 else 0 end)

as total from

(

SELECT TIMESTAMPDIFF(YEAR, dob, NOW()) AS age_group,gender

FROM emp

) as t group by age_grp