Group by multiple columns : SQL

Question

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?

Which dbms are you using?

jarlh
– jarlh

2022-01-20 16:03:10 +00:00
Commented Jan 20, 2022 at 16:03 — jarlh
– jarlh, Commented Jan 20, 2022 at 16:03

Zakaria · Accepted Answer · 2022-01-20 15:42:44Z

3

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

answered Jan 20, 2022 at 15:42

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Md. Tarikul Islam Soikot · Accepted Answer · 2022-01-20 16:11:38Z

1

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

edited Jan 20, 2022 at 16:11

answered Jan 20, 2022 at 16:00

Md. Tarikul Islam Soikot

6651 gold badge8 silver badges14 bronze badges

Comments

user8193706 · Accepted Answer · 2022-01-20 16:32:05Z

1

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

edited Jan 20, 2022 at 16:32

answered Jan 20, 2022 at 15:50

user8193706

2,4552 gold badges11 silver badges14 bronze badges

1 Comment

user8193706 Over a year ago

Sorry @jarlh sir. Updated my answer.

Collectives™ on Stack Overflow

Group by multiple columns : SQL

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related