Sum based on specific condition - Oracle

Question

I need your advice on the following query that I have - Let's say that I have a table with all payments that are booked on my current account. The details of the payment contain date of the operation and hour. I would like to extract the information in a such a way so to have next to each transaction the amount of of the balance(sum of transactions' amount) since the beginning of the day up to the current transaction. The balance for each day is reset to 0.

I was thinking to join this table to itself and find all unique operations from the joined table where the date matches and the hour is less then currently reviewed operation's hour then to use sum on the group.

Still I think that there is much more intelligent solution.

Thanks in advance

here is a sample of the table. Expected result is in the last column

Please include sample table structure and data. You could use SQLFiddle.com to show us the structure and data. Also include expected results in your question — zedfoxus
– zedfoxus, Commented Sep 23, 2015 at 19:38

Justin Cave · Accepted Answer · 2015-09-23 19:47:22Z

2

My guess is that you just want a rolling sum. Making up column names and table names, you probably want something like this in your projection (your select list). You shouldn't need to do a self-join.

SUM(transaction_amount) 
  OVER (PARTITION BY account_number, trunc(transaction_date)
            ORDER BY transaction_date) rolling_sum

answered Sep 23, 2015 at 19:47

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sum based on specific condition - Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related