#1
  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. #2
  3. 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.
  4. #3
  5. 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

IMN logo majestic logo threadwatch logo seochat tools logo