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

CodePudding user response：

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

CodePudding user response：

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