Search code examples
mysqlsqlcumulative-sum

Cumulative (Running) Total in MySQL

I have this query for my database

SELECT FlightNumber, FlightTime FROM flight_log

which outputs the Flight Number and the Flight Time:

Flight Number FlightTime
1 3
2 5
3 2
4 4

I am wondering if there is a way to add a cumulative total to the end of that so I receive the output:

FlightNumber FlightTime CumulativeTotal
1 3 3
2 5 8
3 2 10
4 4 14

The columns are titled FlightNumber and FlightTime, and the table is called Flight_log. Help is greatly appreciated. Thanks

Solution

  • On MySQL 8+, we can use SUM() as an analytic function:

    SELECT FlightNumber, FlightTime,
       SUM(FlightTime) OVER (ORDER BY FlightNumber) AS CumulativeTotal
FROM flight_log
ORDER BY FlightNumber;

    On earlier versions of MySQL, we can use a correlated subquery:

    SELECT FlightNumber, FlightTime,
       (SELECT SUM(f2.FlightTime)
        FROM flight_log f2
        WHERE f2.FlightNumber <= f1.FlightNumber) AS CumulativeTotal
FROM flight_log f1
ORDER BY FlightNumber;