I have a table which includes Years and Genders. Here is an example of my table:
YEAR GENDERS 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?
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.
(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.)