SQL query to find count of males and females city wise

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 1

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

Answer 2

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

Answer 3

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;