Group by multiple columns : SQL-CodePudding

I have a table :

I have a query which gives me

And I want something like this :

Query used for above result is :

select ucountry,sum(Males) Males,sum(females ) Females from (
                                                  select ucountry,
                                                         case when gender = 'M' then count(1) else 0 end as Males,
                                                         case when gender = 'F' then count(1) else 0 end as females
                                                  from testing.test_users
                                                  group by ucountry, gender
                                              ) a group by ucountry;

I am definitely not doing the best thing here. Any thing you guys think would be better?

CodePudding user response：

If you're trying to count the number of males and females in each country:

select ucountry,
sum(case when gender = 'M' then 1 else 0 end) as males,
sum(case when gender = 'F' then 1 else 0 end) as females
from testing.test_users
group by ucountry

CodePudding user response：

You should apply GROUP BY only on ucountry column. Use below query to get expected result in SQL Server:

SELECT  
    ucountry, 
    SUM(IIF(Name = 'M', 1, 0)) males,
    SUM(IIF(Name = 'F', 1, 0)) females
FROM testing.test_users
GROUP BY ucountry

CodePudding user response：

If you are using PostgreSQL then you can also user FILTER

select ucountry, COUNT(*) FILTER (WHERE gender = 'M') males, 
COUNT(*) FILTER (WHERE gender = 'F') females from testing.test_users group by ucountry