I have a table that has amounts transactions for each user. I am trying to query a table that also shows a 'balance' column.

So in the picture above, I have sorted by DATE, now, I would like another column that calculates the balance. So in balance column, starting with the last record (earliest record), it will start off with $150 on that row, then the next row will calculate the current amount - previous amount.

So it should look something like this:

Is there a way to do this?

CodePudding user response：

So with a CTE for the data, you can use the WINDOW FUNCTUON version of SUM to get the result you want.

WITH data(number_id, value, date) AS (
    SELECT column1, column2, to_date(column3, 'YYYY-MM-DD') FROM VALUES
    (1, 10, '2022-01-01'),
    (1, 20, '2022-01-02'),
    (1, 30, '2022-01-04'),
    (1, 40, '2022-01-07'),
    (2, 110, '2022-01-01'),
    (2, 120, '2022-01-02'),
    (2, 130, '2022-01-04'),
    (2, 140, '2022-01-07')
)
SELECT number_id
    ,value
    ,sum(value)over(partition by number_id order by date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as balance
    date
FROM data
ORDER BY 1, 4;

gives:

With your numbers (and 72 corrected to -72):

WITH data(number_id, value, date) AS (
    SELECT column1, column2, to_date(column3, 'YYYY-MM-DD') FROM VALUES
    (111, -22, '2021-12-24'),
    (111, 22, '2021-12-23'),
    (111, -10, '2021-12-22'),
    (111, -30, '2021-12-21'),
    (111, 5, '2021-12-20'),
    (111, -48, '2021-12-19'),
    (111, 5, '2021-12-18'),
    (111, -72, '2021-12-17'),
    (111, 150, '2021-12-16')
)
SELECT number_id
    ,value
    ,sum(value)over(partition by number_id order by date rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as balance
    ,date
FROM data
ORDER BY 1, 4 DESC;

gives: