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?

Can you provide the rows of the statement table where the customer is 7? Also I assume the first query's output is not what you expect either? — Brian DeMilia, Aug 01 '15 at 16:54
If you are using `sql server 2012` or greater then you need to consider Jpw's answer — Pரதீப், Aug 01 '15 at 17:06

jpw · Answer 1 · 2015-08-01T17:10:08.697

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

score 3 · Accepted Answer · edited Aug 01 '15 at 17:13

In CROSS APPLY add following in the WHERE:

AND x.CustID = t.CustID

Finally I got what you need. To make this you need to filter by customer also. Otherwise the cumulative balance is made on whole set and after that you show only customer 7.

After the change you will have cumulative balance only for customer 7.

Proper SQL:

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 x.CustID = t.CustID
    ) b
where t.CustID='7'
ORDER BY t.Number

Sum only the selected rows

2 Answers2