SELECT
  SE.SageEntityID,
  SE.SageEntityName,
  SE.EntityCode,
  SE.InvoicePrefix,
  Y.ClientCount,
  SE.Enabled
FROM
  taSageEntity AS SE
    LEFT JOIN (
      SELECT X.AccountBookID AS SageEntityID, COUNT(*) AS ClientCount FROM (
        SELECT CP.AccountBookID, CP.ClientID FROM taClientProducts AS CP WHERE (CP.AccountBookType = 'Sage') AND (CP.Status = 1)
        UNION
        SELECT BM.AccountBookID, BM.ClientID FROM taAccountBookMember AS BM WHERE (BM.AccountBookType = 'Sage')
      ) AS X GROUP BY 1
    ) AS Y ON (Y.SageEntityID = SE.SageEntityID)
