SELECT
  BD.BilldozerEntityID,
  BD.EntityName,
  BD.ApiKey,
  Y.ClientCount,
  BD.Enabled
FROM
  taBilldozerEntity AS BD
    LEFT JOIN (
      SELECT X.AccountBookID AS BilldozerEntityID, COUNT(*) AS ClientCount FROM (
        SELECT CP.AccountBookID, CP.ClientID FROM taClientProducts AS CP WHERE (CP.AccountBookType = 'Xero') AND (CP.Status = 1)
        UNION
        SELECT BM.AccountBookID, BM.ClientID FROM taAccountBookMember AS BM WHERE (BM.AccountBookType = 'Xero')
      ) AS X GROUP BY 1
    ) AS Y ON (Y.BilldozerEntityID = BD.BilldozerEntityID)
