Dubraven93 Dubraven93 - 1 month ago 9
SQL Question

SQL ORACLE: group by column, get counts from other column depending on different where clauses

I will explain by example:
Say I have a dataset like this:

id | city | age | gender
1 | London | Y | M
2 | Milan | Y | F
3 | London | O | M
4 | London | O | F


I want to have a row for each city, one for London and one for milan.

In each row I need to have a column for each of these:


  • the total number of entries for each city

  • the total number of young (age = Y) for each city

  • the total number of old (age = O) for each city

  • the total number of males (gender = M) for each city

  • the total number of female (gender = F) for each city.



The end result should be like this:

city | n_id n_Y n_O n_M n_F
---------------------------------------
London | 3 1 2 2 1
Milan | 1 1 0 0 1


Any help will be great.

Edit: so far I have

SELECT city, COUNT(id) FROM tablename GROUP BY city

Answer

Use conditional aggregation:

select city, count(*) as n_id,
       sum(case when age = 'Y' then 1 else 0 end) as n_Y,
       sum(case when age = 'O' then 1 else 0 end) as n_O,
       sum(case when gender = 'M' then 1 else 0 end) as n_M,
       sum(case when gender = 'F' then 1 else 0 end) as n_F
from t
group by city;