enter image description hereI am trying to write an SQL query to get below output.

table has below data:

   ID   GENDER 
    10  M 
    10  F 
    10  F 
    20  F 
    20  M 

Output:

    ID  Male    Female 
    10   1       2 
    20   1       1

do i need to use case with group by. Can someone help here.

CodePudding user response：

use simple group

select id,sum(case when GENDER = 'M' then 1 else 0 end) as Male,
          sum(case when GENDER = 'F' then 1 else 0 end) as FeMale
       from tablename
group by id