CODEWITHSUNDEEP
sqloracle11g
Dubraven
Dubraven

Reputation: 1071

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

Upvotes: 0

Views: 160

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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;

Upvotes: 2

Related Questions