How to Calculate the Difference Between Rows in a MySQL SELECT Statement?

Calculating Differences Between Rows in a MySQL SELECT Statement

Problem:

How to determine the difference in values between two subsequent rows in a MySQL SELECT statement? The provided table contains data such as kilometers, date, and car identification. The goal is to create a view that includes an additional column indicating the number of kilometers since the previous date.

Solution:

To achieve this, a combination of LEFT JOIN and subquery is employed. The LEFT JOIN allows us to retrieve data from the myTable table, while the subquery ensures that the comparison is made with the most recent row that has a date prior to the current row. This approach effectively produces the desired difference in kilometers:

SELECT
    mt1.ID,
    mt1.Kilometers,
    mt1.date,
    mt1.Kilometers - IFNULL(mt2.Kilometers, 0) AS number_km_since_last_date   
FROM
    myTable mt1
    LEFT JOIN myTable mt2
        ON mt2.Date = (
            SELECT MAX(Date)
            FROM myTable mt3
            WHERE mt3.Date < mt1.Date
        )
ORDER BY mt1.date

Alternative Solution:

Another method involves emulating the lag() function using MySQL hackiness:

SET @kilo=0;

SELECT
    mt1.ID,
    mt1.Kilometers - @kilo AS number_km_since_last_date,
    @kilo := mt1.Kilometers Kilometers,
    mt1.date
FROM myTable mt1
ORDER BY mt1.date

Results:

Both methods successfully calculate the difference in kilometers between successive rows in the sorted table, resulting in a view that provides the desired additional information.

The above is the detailed content of How to Calculate the Difference Between Rows in a MySQL SELECT Statement?. For more information, please follow other related articles on the PHP Chinese website!