have trouble when table.field_name is needed in two columns based on different values

Question

I have a person table that has a sex field and a few other fields. Looks like this

firstname lastname sex birthday
--------- -------- --- ---------
john       doe      0   1960-01-25
jane       doe      1   1990-02-01
john       smith    0   1995-03-15
mary       smith    1   1990-01-16

so sex = 0 means male sex = 1 means female.

I'd like to see this as a result assuming the current_date as 2014-02-04

Age Female Male
--- ------ ----
18     0     1
24     2     0
54     0     1

I have this

SELECT count(*) AS Female, 
       cast(DATEDIFF(CURRENT_DATE,person.birthday)/(365.256366) AS SIGNED) AS Age
FROM person
WHERE person.sex=1
GROUP BY Age

which gives me the above result without the Male col. I can do a similar one for Male and Age but no Female. How do merge the two to get all three columns?

Answer 1

You can do that as a conditional SUM:

SELECT SUM(CASE WHEN person.sex=1 THEN 1 ELSE 0 END) AS Female, 
       SUM(CASE WHEN person.sex=0 THEN 1 ELSE 0 END) AS Male,
       cast(DATEDIFF(CURRENT_DATE,person.birthday)/(365.256366) AS SIGNED) AS Age
FROM person
GROUP BY Age

BTW your "age" calculation will be close but will not be right when you're on someone's exact birthday day.