Clash Royale CLAN TAG#URR8PPP

Counting two different values for each year

I have a table which includes Years and Genders. Here is an example of my table:

YEAR Sex
1999 M
1999 M
1999 F
1999 F


I use this query for taking my result.

SELECT YEAR,COUNT(*)
FROM athlete_events
WHERE SEX = 'M'
GROUP BY YEAR;


And I see this result on output:

YEAR COUNT(*)
1999 2


However I want to see this result:

YEAR COUNT_MALE COUNT_FEMALE
1999 2 2


Is that possible in Pracle SQL?

3 Answers
3

You can do conditional aggregation with a case expression:

select year,
count(case when sex = 'M' then year end) as count_male,
count(case when sex = 'F' then year end) as count_female
from athlete_events
group by year;

YEAR COUNT_MALE COUNT_FEMALE
---------- ---------- ------------
1999 2 2


The count function ignores nulls, so rows that don't match the specified flag (which default to null in the case expression; you can have an explicit else null if you prefer) are not counted.

else null

(Personally I prefer to use count() rather than sum() for this sort of thing as that better reflects what you're actually doing - counting things.)

count()

sum()

You need conditional aggregation :

select year,
sum(case when SEX = 'M' then 1 else 0 end) as count_male,
sum(case when SEX = 'F' then 1 else 0 end) as count_female
from athlete_events
group by year;


You can do subqueries, something like this:

SELECT
E.YEAR,
(select count(*) from athlete_events M where M.YEAR=E.YEAR and M.SEX='M') COUNT_MALE,
(select count(*) from athlete_events F where F.YEAR=E.YEAR and F.SEX='F') COUNT_FEMALE
FROM athlete_events E
GROUP BY E.YEAR;


This is more expensive (performance-wise) than conditional aggregation, on the other hand maybe easier to understand and maintain.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.