Grabbing count of something, of items where count = 0

Dev Shed Forums Sponsor:

October 9th, 2012, 07:15 PM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 509

Time spent in forums: 4 Days 7 h 39 m 38 sec
Reputation Power: 9

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?

October 10th, 2012, 03:20 AM

swampBoogie

Contributing User

Join Date: Jan 2003

Location: Paris Uppland

Posts: 2,349

Time spent in forums: 1 Month 4 Days 7 h 10 m 28 sec
Reputation Power: 390

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.

January 4th, 2013, 05:24 PM

jaeSun

got Rice?

Join Date: Nov 2004

Posts: 509

Time spent in forums: 4 Days 7 h 39 m 38 sec
Reputation Power: 9

late reply, but thanks! got it working

Viewing: Dev Shed Forums > Databases > PostgreSQL Help > Grabbing count of something, of items where count = 0

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump