Multiple sums across a join

from the Artful Common Queries page

You have a parties table that holds info on peoples' names etc, and a contracts table in which each row defines one contract, identifying a client as clientpartyID and a contractor as contractorpartyID, each of these a foreign key referencing parties.partyID. You want a list of parties showing how many contracts they have participated in as client, and how many they've participated in as contractor. 
SELECT 
  p.partyID, p.name,
  (SELECT COUNT(*) FROM contractor_client c1 
   WHERE c1.clientpartyID = p.partyID 
  ) AS ClientDeals,
  (SELECT COUNT(*) FROM contractor_client c2 
   WHERE c2.contractorpartyID = p.partyID
  ) AS ContractorDeals
FROM parties p
ORDER BY partyID;

Last updated 22 May 2009