Dubraven93 - 6 months ago 29

SQL Question

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