CODEWITHSUNDEEP
mysqlsqlgroup-by
Jin Kwon
Jin Kwon

Reputation: 22007

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

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

Upvotes: 3

Views: 61

Answers (2)

haMzox
haMzox

Reputation: 2109

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;

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

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

Upvotes: 1

Related Questions