Question: Let's say we have the following table:

```
city    gender
 abc    m
 abc    f
 def    m

```

Required output:

---

```
city    f_count   m_count
 abc    1          1
 def    0          1

```

Please help me in writing a query either in Hive or MySQL or SQL Server syntax. Hive syntax is needed for me.

Thank You:)

Answer: 
SQL Server:

```
select 
 city
,count(case when gender = 'm' then 1 else null end) as m_count
,count(case when gender <> 'm' then 1 else null end) as f_count
from table_name 
group by city

```

> 
> Result:
> 
> 
> 
> ```
> city  | f_count   | m_count
> abc   |      1    |    1
> def   |      0    |    1
> 
> ```
> 
>
```
select city, sum(male),sum(female) from
(select city ,count(gender) as male,0 as female from temp where gender='M' group by city 
union 
select city , 0,count(gender) as female from temp where gender='F'group by city)
group by city; 

```