CODEWITHSUNDEEP
sqlgroup-byaggregate
Durvesh Patil
Durvesh Patil

Reputation: 41

Group by multiple columns : SQL

I have a table :

enter image description here

I have a query which gives me

enter image description here

And I want something like this :

enter image description here

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?

Upvotes: 0

Views: 292

Answers (3)

user8193706
user8193706

Reputation: 2425

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

Upvotes: 1

Md. Tarikul Islam Soikot
Md. Tarikul Islam Soikot

Reputation: 595

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

Upvotes: 1

Zakaria
Zakaria

Reputation: 4806

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

Upvotes: 3

Related Questions