### Thread: Grabbing count of something, of items where count = 0

1. No Profile Picture
got Rice?
Devshed Novice (500 - 999 posts)

Join Date
Nov 2004
Posts
518
Rep Power
11

#### Grabbing count of something, of items where count = 0

Trying to get a count of something, including count of items where count = 0

the example here is a simplified of the issue that I am working on.

Code:
```Table 1:  client
clientid|name|genderid|someotherid
-----------------
1|John|2|2
2|Jacob|2|2
3|Jason|2|0
4|Alice|3|0

Table 2:  gender
genderid|gender
-----------------
1|Alien
2|Male
3|Female```
I do SQL to get a total:

Code:
```SELECT Count(c.genderid),
g.gender
FROM   gender g
LEFT JOIN client c
ON c.genderid = g.genderid
GROUP  BY c.genderid,
g.gender```
Results are like:

Code:
```Alien|0
Male|3
Female|1```
but if I add a WHERE statement, such as this:

Code:
```SELECT Count(c.genderid),
g.gender
FROM   gender g
LEFT JOIN client c
ON c.genderid = g.genderid
WHERE  c.someotherid = 2
GROUP  BY c.genderid,
g.gender```
then the results are like:

Code:
`2|Male`
Female and Alien are not part of the results? How do I get it to include Female and Alien totals?

I found that I could do this:

Code:
```SELECT Count(c.genderid),
g.gender
FROM   gender g
LEFT JOIN client c
ON c.genderid = g.genderid
WHERE  c.someotherid = 2
GROUP  BY c.genderid,
g.gender
UNION
SELECT 0,
g2.gender
FROM   testgender g2
WHERE  g2.genderid NOT IN (SELECT g.genderid
FROM   gender g
LEFT JOIN client c
ON c.genderid = g.genderid
WHERE  c.someotherid = 2)```
I am sure there is a better way of doing this?
2. No Profile Picture
Contributing User
Devshed Regular (2000 - 2499 posts)

Join Date
Jan 2003
Location
Paris Uppland
Posts
2,385
Rep Power
391
Code:
```select count(c.genderid),
g.gender
from gender g
left
join client c
on c.genderid = g.genderid
and c.someotherid = 2
group
by g.gender```
The unmatched columns from the client table are null and thus the where clause will exclude those records.
3. No Profile Picture
got Rice?
Devshed Novice (500 - 999 posts)

Join Date
Nov 2004
Posts
518
Rep Power
11
late reply, but thanks! got it working