Imagine I have this:
| id | transactiondate | transactiontag | clientid | transactionvalue |
|---|---|---|---|---|
| 1 | 2020-12-28 | pizza | 0 | 29.99 |
| 2 | 2020-12-29 | payment | 0 | -39.99 |
| 3 | 2020-12-28 | cheese | 0 | -109.99 |
| 4 | 2020-12-28 | cheese | 0 | -109.99 |
| 5 | 2020-12-28 | pizza | 1 | -19.99 |
| 6 | 2020-12-28 | cheese | 1 | -18.99 |
| 7 | 2020-12-28 | salary | 1 | 58.99 |
| 8 | 2020-12-29 | salary | 1 | 38.99 |
So I have all the Ids of transactions, when they were made by whom and how much they paid, what I needed to do is a cumulative sum for each date and for each individual, so the answer should be this:
this code -> SELECT distinct accountid, transactiondate, SUM(transactionvalue) OVER(ORDER BY transactiondate) AS cumulative_sum FROM acc_transactions where accountid = 1; gives me this
| clientid | date | cumulative_sum |
|---|---|---|
| 1 | 2020-12-28 | 20.01 |
| 1 | 2020-12-29 | 59.00 |
the thing is that I need this both for clientid 1 and 0, tried everything I could so far! Need a hero rightnow haha!
Example of right answer ↓
| clientid | date | cumulative_sum |
|---|---|---|
| 1 | 2020-12-28 | 20.01 |
| 1 | 2020-12-29 | 59.00 |
| 0 | 2020-12-28 | -189.99 |
| 0 | 2020-12-29 | -229.98 |
Advertisement
Answer
First you want a daily aggregation:
select accountid, transactiondate,
sum(transactionvalue) as transactionvalue
from acc_transactions
group by accountid, transactiondate
order by accountid, transactiondate
Then, you can use window functions on top of it:
select accountid, transactiondate,
sum(sum(transactionvalue))
over(partition by accountid order by transactiondate)
as cumulative_sum
from acc_transactions
group by accountid, transactiondate
order by accountid, transactiondate