Counting two different values for each year

  • A+
Category:Languages

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.)

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: