How can I separate two columns of count by group by?

Question

When I do this,

SELECT id, gender, count(1)
FROM class_student
WHERE id = 1
GROUP BY gender

I get

id  gender    count
--------------------
1   female    10
1   male      5

How can I get this?

id female male
---------------
1  10     5

Is following query is the right way to do this?

SELECT
    id,
    (select count(1) from class_student
     where id = 1 AND gender='female') AS female,
    (select count(1) from class_student
     where id = 1 AND gender='male') AS male
FROM class_student
GROUP BY id

Check this answer http://stackoverflow.com/a/12005676/5079380 — Amr Eladawy, May 02 '17 at 05:57

ScaisEdge · Accepted Answer · 2017-05-03T16:00:10.340

1

you can use a select with case when

  SELECT id
         , sum(case when gender = 'male' then 1 else 0 end) male, 
         , sum(case when gender = 'female' then 1 else 0 end) female
    FROM class_student
    WHERE id = 1
    GROUP BY id

edited May 03 '17 at 16:00

answered May 02 '17 at 05:51

ScaisEdge

131,976
10
91
107

1

Shouldn't it be `GROUP BY id`? – Jin Kwon May 03 '17 at 10:09

haMzox · Answer 2 · 2017-05-03T16:01:48.317

1

You can use CASES:

SELECT id, SUM(case when gender = 'male' then 1 else 0 end) male, SUM(case when gender = 'female' then 1 else 0 end) female
    FROM CLASS_STUDENT
    WHERE id = 1
    GROUP BY id;

edited May 03 '17 at 16:01

answered May 02 '17 at 05:57

haMzox

2,073
1
12
25

How can I separate two columns of count by group by?

2 Answers2