Multiple sums across a join

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;