SQL - get rid of the nested aggregate select

Question

There is a table Payment, which for example tracks the amount of money user puts into account, simplified as

===================================
Id | UserId |  Amount |   PayDate |
===================================
 1 |     42 |      11 |  01.02.99 |
 2 |     42 |      31 |  05.06.99 |
 3 |     42 |      21 |  04.11.99 |
 4 |     24 |      12 |  05.11.99 |

What is need is to receive a table with balance before payment moment, eg:

===============================================
Id | UserId |   Amount |   PayDate |  Balance |
===============================================
 1 |     42 |       11 |  01.02.99 |        0 |
 2 |     42 |       31 |  05.06.99 |       11 |
 3 |     42 |       21 |  04.11.99 |       42 |
 4 |     24 |       12 |  05.11.99 |        0 |

Currently the select statement looks something like

SELECT 
    Id,
    UserId,
    Amount,
    PaidDate,
    (SELECT sum(amount) FROM Payments nestedp 
      WHERE nestedp.UserId = outerp.UserId AND
            nestedp.PayDate < outerp.PayDate) as Balance
FROM
    Payments outerp

How can I rewrite this select to get rid of the nested aggregate selection? The database in question is SQL Server 2019.

You have the answer here: https://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — Carlos, Nov 12 '21 at 15:32
Yes, since SQL Server 2012 SQL Server has supported "rolling/cumulative aggregates" with the use of the `OVER` clause. — Thom A, Nov 12 '21 at 15:33
20 years after Y2k. NO ONE should be using 2-digit years for anything. — SMor, Nov 12 '21 at 16:01
@SMor, except for a simplified example on stackoverflow, maybe? — Timofeus, Nov 12 '21 at 16:04
How does that specific simplification benefit anyone? (Other than the obvious "I didn't have to type two characters.") — Aaron Bertrand, Nov 12 '21 at 16:05

score 1 · Answer 1 · answered Nov 12 '21 at 16:56

1

You need to use cte with some custom logic to handle this type of problem.

WITH PaymentCte
AS (
    SELECT ROW_NUMBER() OVER (
            PARTITION BY UserId ORDER BY Id
            ) AS RowId
        ,Id
        ,UserId
        ,PayDate
        ,Amount
        ,SUM(Amount) OVER (
            PARTITION BY UserId ORDER BY Id
            ) AS Balance
    FROM Payment
    )
SELECT X.Id
    ,X.UserId
    ,X.Amount
    ,X.PayDate
    ,Y.Balance
FROM PaymentCte x
INNER JOIN PaymentCte y ON x.userId = y.UserId
    AND X.RowId = Y.RowId + 1

UNION

SELECT X.Id
    ,X.UserId
    ,X.Amount
    ,X.PayDate
    ,0 AS Balance
FROM PaymentCte x
WHERE X.RowId = 1

This provides the desired output

answered Nov 12 '21 at 16:56

ishant kaushik

891
6
18

What if id and paydate not necessarily go in order? Change to `SUM(Amount) OVER (PARTITION BY UserId ORDER BY PayDate ) AS Balance`? – Timofeus Nov 12 '21 at 17:20
@Timofeus Did you run after updating this in above query at your end? I don't think it will give desired output which you have mentioned in your output table – ishant kaushik Nov 12 '21 at 17:57
@Timofeus If you want Order By PayDate as your order by column for Sum then output balance for Id=3 will be 63 – ishant kaushik Nov 12 '21 at 18:00

score 0 · Answer 2 · answered Nov 12 '21 at 17:21

0

You can try the following using lag with a cumulative sum

 with b as (
  select * , isnull(lag(amount) over (partition by userid order by id),0) Amt
    from t
 )
 select Id, UserId, Amount, PayDate, 
   Sum(Amt) over (partition by userid order by id) Balance
 from b
 order by Id

answered Nov 12 '21 at 17:21

Stu

30,392
6
14
33

Did you test this? I tried running this and I am not getting 0 as Balance for Id 1 and 4 – ishant kaushik Nov 12 '21 at 18:06
@ishantkaushik Yes of course, it produces your desired results, see [this example Fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=dcf41859454261bd80256407c6881819) – Stu Nov 12 '21 at 18:21

Timofeus · Accepted Answer · 2021-11-13T06:50:17.777

Thanks to other participants' leads I came up with a query that (seems) to work:

SELECT 
    Id,
    UserId,
    Amount,
    PayDate,
    COALESCE(sum(Amount) over (partition by UserId 
                                order by PayDate 
                                rows between unbounded preceding and 1 preceding), 0) as Balance
FROM
    Payments
ORDER BY
    UserId, PayDate

Lots of related examples can be found here

SQL - get rid of the nested aggregate select

3 Answers3