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:)
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
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;