Count with condition using MySQL

Question

I am new to mySQL.

I want to show the table with count for the following table as shown below:

Table: test

create table test
(
age int,
sex text
);

Insertion of some records:

insert into test values(10,'Male');
insert into test values(15,'Male');
insert into test values(20,'Male');
insert into test values(25,'Male');
insert into test values(30,'Male');
insert into test values(10,'Female');
insert into test values(15,'Female');
insert into test values(40,'Male');
insert into test values(50,'Female');
insert into test values(60,'Female');

Note: Now I want to show the count of male and female between the age range in the table.

Expected Result:

 Age-Range   sex    count
 ------------------------
  10-20      Male    3
  10-20      Female  2
  25-40      Male    3
  45-60      Female  2

http://stackoverflow.com/questions/19848930/group-by-case-statement — vp_arth, Dec 11 '14 at 06:39

score 2 · Accepted Answer · answered Dec 11 '14 at 07:47

Although counting the males and females from a set is pretty easy in mysql. You can use conditional sum ex.

select 
age,
sum(sex='Male') as male_count
sum(sex='Female') as female_count
from test
group by age ;

But in your case you need to consider the range as well, so its not straight forward. Here is a way you can achieve what you are looking at, but there may be other better way to do it.

select 
p.`range`,
p.sex,
sum(p.`count`) as `count`
from
(
 select 
  x.`range`,
  y.`sex`,
  y.`age`,
  y.`count` from( 
    select '10-20' as `range`,20 as `upper` ,10 as `lower`
    union all 
    select '25-40' as `range`,40 as `upper` ,25 as `lower`
    union all 
    select '45-60' as `range`,60 as `upper` ,45 as `lower`
  )x
  left join(
    select
    sex,
    count(*) as `count`,
    age from test
    group by age,sex

  )y
  on y.age >= x.`lower` and y.age <= x.`upper`
)p

group by p.`range`,p.sex
;

Count with condition using MySQL

1 Answers1