Three Powerful Ways to Perform Cumulative Queries in SQL

When you need to compute cumulative totals in a single SQL query, there are three common approaches.

1. Subquery (compatible with MySQL versions without window functions)

SELECT A.OrderDate,
       (SELECT SUM(B.Amount)
        FROM ADD_NUM B
        WHERE B.OrderDate <= A.OrderDate) AS Amount
FROM ADD_NUM A;

2. Cartesian product (cross join) with GROUP BY

SELECT A.OrderDate,
       SUM(B.Amount) AS Amount
FROM ADD_NUM A
CROSS JOIN ADD_NUM B
WHERE B.OrderDate <= A.OrderDate
GROUP BY A.OrderDate;

3. Window function (available in MySQL 8.0+)

SELECT OrderDate,
       SUM(Amount) OVER (ORDER BY OrderDate) AS Amount
FROM ADD_NUM;

Choose the method that matches your MySQL version and performance requirements: subqueries work everywhere, the Cartesian product can be useful for older versions, and window functions provide the most concise and efficient solution when supported.