I have a table that contains transaction amounts. I need to sum each transaction to calculate the total sum amount.

This is the table:

I have this query:

sum(AMOUNT) over(partition by USER_KEY order by DATE rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as TOTAL_BALANCE_AMOUNT from TABLE1

However, when I do this I get 75, instead of 0. Does anyone know how to fix this? The dates can also be the same for each transaction.

When I run the query below, this is the output I am getting:

select USER_KEY, 
sum(AMOUNT) over(partition by USER_KEY
                        order by DATE rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as TOTAL_BALANCE_AMOUNT
      from TABLE1

CodePudding user response：

why is the second query, showing 4 different record balances?

That is the point of "running total". If the goal is to have a single value per entire window then order by should be skipped:

select USER_KEY, 
       sum(AMOUNT) over(partition by USER_KEY) as TOTAL_BALANCE_AMOUNT
from TABLE1;

The partition by clause could be futher expanded with date to produce output per user_key/date:

select USER_KEY, 
       sum(AMOUNT) over(partition by USER_KEY,date) as TOTAL_BALANCE_AMOUNT
from TABLE1;

CodePudding user response：

I think you're looking for something like this, aggregate by USER_ID, DATE, and then calculate a running sum. If this is not what you're looking for nor is Lukasz Szozda's answer, please edit the question to show the intended output.

create or replace table T1(USER_KEY int, AMOUNT number(38,2), "DATE" date);

insert into T1(USER_KEY, AMOUNT, "DATE") values
(1001, 75,  '2022-12-02'),
(1001, -75, '2022-12-02'),
(1001, 75,  '2022-12-03'),
(1001, -75, '2022-12-03');

select USER_KEY, "DATE", sum(AMT) 
over(partition by USER_KEY order by DATE rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as TOTAL_BALANCE_AMOUNT from
(select USER_KEY, sum(AMOUNT) as AMT, "DATE" from T1 group by USER_KEY, "DATE");