CODEWITHSUNDEEP
sql
Julie
Julie

Reputation: 33

Count for both Male and Female for a specific ID

I have a database of animals with their sex and the cage no they are kept in. I want to count the number of females and males in each cage e.g.

Animal    CageNo   Sex
Dog2       01       M
Dog1       01       F
Cat1       03       F

SELECT cageno, COUNT(sex) AS 'Female'
FROM animal
WHERE sex='F'
GROUP BY cageno

I want one column with the CageNo, count of Females in cage, count of Males in cage E.g.

CageNo    Female    Male
01        1         1
03        1         0

Upvotes: 1

Views: 151

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use conditional aggregation:

SELECT cageno,
       SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) AS Female,
       SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) AS Male
FROM animal
GROUP BY cageno;

You should only use single quotes for string and date constants. You do not need to escape these names. If you do, use the appropriate escape character for your database (double quotes, back ticks, or square braces).

Upvotes: 2

Related Questions