Two counts in one query? 1

[OsakaWebbie]

I have a table "person" that is a misnomer because it actually holds both people and the organizations they can belong to. Another table called "perorg" holds those membership relationships by having columns PersonID and OrgID that both point to the PersonID field in the person table. I'm currently trying to find which organizations have the most members that fit certain criteria in other related tables. Counting one set of criteria, I'm fine - this query works to find the number of members that were "registered":

SELECT o1.PersonID,o1.FullName,o1.Furigana,COUNT(por.PersonID) numreg
  FROM person o1 JOIN perorg por ON o1.PersonID=por.OrgID
  JOIN percat pcr ON por.PersonID=pcr.PersonID AND pcr.CategoryID IN (6,53)
GROUP BY o1.PersonID,o1.FullName,o1.Furigana ORDER BY numreg DESC

And this query works to find the number of members "sent":

SELECT o1.PersonID,o1.FullName,o1.Furigana,COUNT(pos.PersonID) numsent
  FROM person o1 JOIN perorg pos ON o1.PersonID=pos.OrgID
  JOIN percat pcs ON pos.PersonID=pcs.PersonID AND pcs.CategoryID IN (6,53)
  AND pos.PersonID IN (SELECT PersonID FROM attendance
    WHERE AttendDate <= DATE(CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','+9:00'))
    AND EventID=2)
GROUP BY o1.PersonID,o1.FullName,o1.Furigana ORDER BY numsent DESC

What I want to do is show both numbers, but I can't seem to figure out how to combine them in one query - I'm out of my league, but I'm trying anyway. I tried this:

SELECT o1.PersonID,o1.FullName,o1.Furigana,COUNT(por.PersonID) numreg,COUNT(pos.PersonID) numsent
  FROM person o1 JOIN perorg por ON o1.PersonID=por.OrgID
  JOIN percat pcr ON por.PersonID=pcr.PersonID AND pcr.CategoryID IN (6,53)
  JOIN perorg pos ON o1.PersonID=pos.OrgID
  JOIN percat pcs ON pos.PersonID=pcs.PersonID AND pcs.CategoryID IN (6,53)
  AND pos.PersonID IN (SELECT PersonID FROM attendance
    WHERE AttendDate <= DATE(CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','+9:00'))
    AND EventID=2)
GROUP BY o1.PersonID,o1.FullName,o1.Furigana ORDER BY numsent DESC

But the counts are multiplying instead of staying separate - I can see why they are, but I don't know how to fix it. Is there a way to do this in one query? Or will I have to just get one of the counts (probably "sent", since that's what I want to order it by to see the top-ranking orgs) and then loop through in PHP and send a query for each org to get its "registered" count?

 

[r937]

SELECT o1.PersonID
     , o1.FullName
     , o1.Furigana
     , COUNT(pos.PersonID) numreg  
     , COUNT(
       CASE WHEN pos.PersonID IN 
                ( SELECT PersonID 
                    FROM attendance  
                   WHERE AttendDate <= DATE(CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','+9:00'))    
                     AND EventID = 2)
            THEN 'sent' 
            ELSE NULL END ) AS numsent
  FROM person o1 
INNER  
  JOIN perorg pos 
    ON pos.OrgID = o1.PersonID
INNER  
  JOIN percat pcs 
    ON pcs.PersonID = pos.PersonID
   AND pcs.CategoryID IN (6,53)  
GROUP 
    BY o1.PersonID
     , o1.FullName
     , o1.Furigana 
ORDER 
    BY numsent DESC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[OsakaWebbie]

Beautiful! CASE was completely new to me - thanks.