running total

 

🎯 Goal:

Calculate a running total of a column (like sales_amount) using:

1. ✅ Self Join

2. ✅ Window Function (modern/easier way)

---

Let's assume a table: daily_sales

sale_date	sales_amount
2025-06-01	100
2025-06-02	120
2025-06-03	130
2025-06-04	110

---

🔁 1. Running Total Using Self Join (Old School SQL)

sql
SELECT 
  a.sale_date,
  a.sales_amount,
  SUM(b.sales_amount) AS running_total
FROM daily_sales a
JOIN daily_sales b 
  ON b.sale_date <= a.sale_date
GROUP BY a.sale_date, a.sales_amount
ORDER BY a.sale_date;

🔍 How it works:

• For each row a, join all rows b that occurred on or before a.sale_date

• Then sum all b.sales_amount

---

⚡ 2. Running Total Using Window Function (Modern SQL)

sql
SELECT 
  sale_date,
  sales_amount,
  SUM(sales_amount) OVER (
    ORDER BY sale_date
  ) AS running_total
FROM daily_sales;

✅ Simpler, faster, and more readable.

Works in: PostgreSQL, SQL Server, Oracle, MySQL 8+, etc.

---

📊 Sample Output:

sale_date	sales_amount	running_total
2025-06-01	100	100
2025-06-02	120	220
2025-06-03	130	350
2025-06-04	110	460