Sum only the selected rows

Question

The below query gets customer statement:

SELECT  t.S_Type,t.Number, t.Debit, t.Credit,t.CustID,b.Balance
FROM    Statement as t
    CROSS apply
    (
        SELECT  Balance = SUM(Debit) - SUM(Credit)
        FROM    Statement as x
        WHERE   x.Number<= t.Number 
    ) b

ORDER BY t.Number

Query result:

type            #    Debit  credit  cid  balance 
Sales Invoice   1   200.00  0.00    3   200.00
Sales Invoice   10  850.00  0.00    3   1050.00
Service Invoice 11  90.00   0.00    21  1140.00
Sales Invoice   12  20.00   0.00    3   1160.00
Sales Invoice   13  200.00  0.00    2   1360.00
Sales Invoice   14  20.00   0.00    9   1380.00
Sales Invoice   15  120.00  0.00    17  1500.00
Sales Invoice   16  100.00  0.00    19  1600.00
Sales Invoice   17  140.00  0.00    20  1740.00
Sales Invoice   18  4250.00 0.00    16  5990.00
Sales Invoice   19  2500.00 0.00    22  8490.00
Sales Invoice   2   100.00  0.00    7   8590.00
Sales Invoice   20  1225.00 0.00    2   9815.00
Sales Invoice   3   200.00  0.00    1   10015.00
Sales Invoice   4   520.00  0.00    2   10535.00
Sales Invoice   5   25.00   0.00    1   10560.00
Sales Invoice   6   160.00  0.00    2   10720.00
Sales Invoice   7   20.00   0.00    7   10740.00
Sales Invoice   9   850.00  0.00    2   11590.00

But I'd like to get the statement for customer with id 7. The query I use for this is:

SELECT  t.S_Type,t.Number, t.Debit, t.Credit,t.CustID,b.Balance
FROM    Statement as t
    CROSS apply
    (
        SELECT  Balance = SUM(Debit) - SUM(Credit)
        FROM    Statement as x
        WHERE   x.Number<= t.Number 
    ) b
where t.CustID='7'
ORDER BY t.Number

This query's result is:

type            #   Debit   credit  cid balance
Sales Invoice   2   100.00  0.00    7   8590.00
Sales Invoice   7   20.00   0.00    7   10740.00

However, that result is wrong. I expect it to be:

balance

100.00

120.00

What's wrong with the query?

Answer 1

To fix your query you need to add the CustID to the correlated cross apply so that the sum only is calculated for the customer in the outer scope and not for all:

SELECT  t.S_Type, t.Number, t.Debit, t.Credit, t.CustID, b.Balance
FROM    Statement AS t
CROSS APPLY
    (
        SELECT  Balance = SUM(Debit) - SUM(Credit)
        FROM    Statement AS x
        WHERE   x.Number <= t.Number AND t.CustID = x.CustID
    ) b
WHERE t.CustID='7'
ORDER BY t.Number

If you're using SQL Server 2012+ a better alternative that also should perform better would be to use sum() as a window function:

SELECT 
    S_Type, Number, Debit, Credit, CustId, 
    Balance = SUM(Debit - Credit) OVER (PARTITION BY CustId ORDER BY Number) 
FROM Statement 
WHERE CustID='7'
ORDER BY Number

Your original query (without the where clause) would be:

SELECT 
 S_Type, Number, Debit, Credit, CustID,
 Balance = SUM(Debit - Credit) OVER (ORDER BY number) 
FROM Statement 
ORDER BY Number