SQL Query Cleanup using COUNT

Question

I have the following working query, however it seems as if there must be a more simple way of writing this. I have cleaned up the query best I could, and pasted it below, I appreciate any help/advice you could give me.

Sample Result:

UserID  | MemberCount | TotalCheck | TotalCCs
---------------------------------------------- 
123     |  75         |  25        | 0 
456     |  74         |  129       | 156

Sample Query:

Select BPE.UserID
    ,ISNULL((Select COUNT(*) 
           From clients 
           where fac_id = BPE.billpay_FacID 
               and clt_web_type = 1 
               and clt_relationship = 0),'0') as MemberCount
    ,(Select SUM(achorder_total) as ACHTotal 
        from ACHOrder 
        where achorder_siteid = BPE.siteID 
                    and ACHOrder_PayDate between @Start and @End 
                    and ACHOrder_Status not in ('Voided','Reversed')) As TotalChecks
    ,(Select SUM(CCorder_total) as CCTotal 
        from CCOrder 
        where CCorder_siteid = BPE.siteID 
                    and CCOrder_PayDate between @Start and @End 
                    and CCOrder_Status not in ('Voided','Reversed')) As TotalCCs
From BillingEnabled BPE
Order By BPE.UserID

Is there a simpler way of constructing this query?

Answer 1

If I understood correctly, you want to count/sum unrelated rowsets independently? I don't think that you have much choice there.

What I would use is CROSS APPLY (or its sister OUTER APPLY). It changes the syntax a bit, but it probably doesn't change the execution plan much (you should check it, though. I'm just guessing here).

SELECT BPE.UserID, MemberCount.Value, TotalChecks.Value
FROM BillingEnabled BPE
CROSS APPLY (SELECT COUNT(*) as Value 
             FROM clients 
             WHERE fac_id = BPE.billpay_FacID and ...) MemberCount
OUTER APPLY (SELECT SUM(achorder_total) as Value 
             FROM ACHOrder 
             WHERE achorder_siteid = BPE.siteID AND ...) TotalChecks
ORDER BY 1

Maybe the execution plan is different if you do the full computations first then join the results. Not sure if this would be more efficient or worse. If you don't query everything but a small subset of users, this would be very inefficient if you don't restrict the first two queries in some way.

WITH MemberCount AS
(
  SELECT fac_id, COUNT(*) as Value
  FROM clients
  WHERE clt_web_type = 1 AND clt_relationship = 0
  GROUP BY fac_id
),
TotalChecks AS
(
  SELECT achorder_siteid, SUM(achorder_total) as Value
  FROM ACHOrder
  WHERE ACHOrder_PayDate BETWEEN @Start AND @End 
    AND ACHOrder_Status NOT IN ('Voided','Reversed')) 
  GROUP BY achorder_siteid
)
SELECT BPE.UserId, MemberCount.Value, TotalChecks.Value
FROM BPE
LEFT OUTER JOIN MemberCount 
  ON MemberCount.fac_id = BPE.billpay_FacID
LEFT OUTER JOIN TotalChecks
  ON TotalChecks.achorder_siteid = BPE.siteID
ORDER BY 1