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

```
select city,sum(gender='f') as f_count,sum(gender='m') as m_count
from my_table
group by city;

```

> 
> Result:
> 
> 
> 
> ```
> city  f_count m_count
> abc         1      1
> def         0      1
> 
> ```
> 
> 

[Demo](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e9e64e17c116766877f1ba802492fac3)
```
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; 

```