How to calculate running total in SQL using self join

Data and Databases
1

I need help with a SQL query that calculates running totals. I have a table named expenses containing daily spending amounts:

transaction_date amount
2023-12-15 100
2023-12-14 80
2023-12-13 100
2023-12-12 120
2023-12-11 100

My goal is to create a result set showing cumulative totals for each date, adding up all previous amounts:

transaction_date cumulative_total
2023-12-15 500
2023-12-14 400
2023-12-13 320
2023-12-12 220
2023-12-11 100

I attempted using a self join like expenses e1 LEFT JOIN expenses e2 ON e1.transaction_date >= e2.transaction_date, but it’s not yielding the expected results. What is the right way to get these running totals?

2

Self joins work, but you’ve got the condition backwards. Change e1.transaction_date >= e2.transaction_date to e1.transaction_date <= e2.transaction_date - you want to sum amounts from the current date back. Here’s the fix: SELECT e1.transaction_date, SUM(e2.amount) as cumulative_total FROM expenses e1 JOIN expenses e2 ON e1.transaction_date <= e2.transaction_date GROUP BY e1.transaction_date ORDER BY e1.transaction_date DESC. I’ve used this tons in older systems without window functions. Not as efficient as modern methods, but it works.

3

hey, looks like your self join is close! just remember to group by the date and sum up the amounts. give this a shot: SELECT e1.transaction_date, SUM(e2.amount) FROM expenses e1 LEFT JOIN expenses e2 ON e1.transaction_date <= e2.transaction_date GROUP BY e1.transaction_date - any error messages you’re getting?

4

window functions are perfect for this. try SELECT transaction_date, SUM(amount) OVER (ORDER BY transaction_date DESC ROWS UNBOUNDED PRECEDING) as cumulative_total FROM expenses ORDER BY transaction_date DESC - way cleaner and faster than self joins.