CODEWITHSUNDEEP
mysqlsql
sunny93
sunny93

Reputation: 131

MySQL GROUP BY not giving desired result

I have to show summary as number of males, number of females in each unit. Tried the following query:

SELECT 
    unit,
    CASE gender
        WHEN 'Male' THEN COUNT(id)
    END AS 'male',
    CASE
        WHEN gender = 'Female' THEN COUNT(id)
    END AS 'female'
FROM
    admission_info
WHERE
    admission_date = '2017-04-15'
GROUP BY gender

Here's the Fiddle which shows the output. But i want it in 1 row where it should show unit name, number of males, number of females.

Upvotes: 0

Views: 38

Answers (2)

Gene Stempel
Gene Stempel

Reputation: 285

SELECT unit, 
sum(CASE gender WHEN 'Male' THEN 1 else 0 END) AS 'male', 
sum(CASE WHEN gender='Female' THEN 1 else 0 END) AS 'female' 
FROM admission_info WHERE admission_date='2017-04-15'
group by unit

Upvotes: 5

Wajih
Wajih

Reputation: 4383

SELECT unit, gender, count(id) as count
FROM admission_info WHERE admission_date='2017-04-15' GROUP BY unit, gender

Upvotes: 1

Related Questions