5 Ways to Calculate the Difference Between Values in the Same Column (But Different Rows) in MySQL

MySQL has a bunch of functions that enable us to get values from another row in the same column. This makes it easy for us to do stuff like calculate the difference between a value in the current row and one in another row, even if it’s in the same column.

Here are five options for calculating the difference between a value in the current row and a value in a different row but in the same column.

Option 1: The `LAG()` Function

We can use the LAG() function to get the value from a previous row in the same column (or in another column if need be). Therefore, we can use this function to help us calculate the difference between a value in the current row with a value in a previous row.

Example:

SELECT 
    IdiotName,
    IQ,
    LAG( IQ, 1 ) OVER win AS "Lag Value",
    IQ - LAG( IQ, 1 ) OVER win AS "Current Minus Previous",
    LAG( IQ, 1 ) OVER win - IQ AS "Previous Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );

Result:

+-------------------+------+-----------+------------------------+------------------------+
| IdiotName         | IQ   | Lag Value | Current Minus Previous | Previous Minus Current |
+-------------------+------+-----------+------------------------+------------------------+
| Dumbest           |   30 |      NULL |                   NULL |                   NULL |
| Homer             |   40 |        30 |                     10 |                    -10 |
| Patrick Star      |   40 |        40 |                      0 |                      0 |
| Ed                |   40 |        40 |                      0 |                      0 |
| Dumber            |   50 |        40 |                     10 |                    -10 |
| Peter Griffin     |   50 |        50 |                      0 |                      0 |
| Cosmo             |   55 |        50 |                      5 |                     -5 |
| Dumb              |   60 |        55 |                      5 |                     -5 |
| Ralph Wiggum      |   65 |        60 |                      5 |                     -5 |
| Richard Watterson |   70 |        65 |                      5 |                     -5 |
+-------------------+------+-----------+------------------------+------------------------+

Here, I used LAG( IQ, 1 ) for the lag value. The IQ part means the IQ column, and 1 means that we go back one row.

In the Current Minus Previous column we subtracted the LAG() value from the IQ column (i.e. the value of the current row in that column). In the Previous Minus Current column we did the opposite – we subtracted the IQ column from the LAG() output.

The lag value in the first row is NULL because there’s no previous row for which to retrieve a value.

Option 2: The `LEAD()` Function

The LEAD() function works similar to LAG(), except that it gets its value from a later row. For example, we can use it to get its value from the next row. By doing this, we can calculate the difference between the current row and the next row.

Example:

SELECT 
    IdiotName,
    IQ,
    LEAD( IQ, 1 ) OVER win AS "Lead Value",
    IQ - LEAD( IQ, 1 ) OVER win AS "Current Minus Next",
    LEAD( IQ, 1 ) OVER win - IQ AS "Next Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );

Result:

+-------------------+------+------------+--------------------+--------------------+
| IdiotName         | IQ   | Lead Value | Current Minus Next | Next Minus Current |
+-------------------+------+------------+--------------------+--------------------+
| Dumbest           |   30 |         40 |                -10 |                 10 |
| Homer             |   40 |         40 |                  0 |                  0 |
| Patrick Star      |   40 |         40 |                  0 |                  0 |
| Ed                |   40 |         50 |                -10 |                 10 |
| Dumber            |   50 |         50 |                  0 |                  0 |
| Peter Griffin     |   50 |         55 |                 -5 |                  5 |
| Cosmo             |   55 |         60 |                 -5 |                  5 |
| Dumb              |   60 |         65 |                 -5 |                  5 |
| Ralph Wiggum      |   65 |         70 |                 -5 |                  5 |
| Richard Watterson |   70 |       NULL |               NULL |               NULL |
+-------------------+------+------------+--------------------+--------------------+

Here, I used LEAD( IQ, 1 ) for the lead value. The IQ part means the IQ column, and 1 means that we go forward one row.

In the Current Minus Next column we subtracted the LEAD() value from the IQ column (i.e. the value of the current row in that column). In the Next Minus Current column we did the opposite – we subtracted the IQ column from the LEAD() output.

The lead value in the last row is NULL because there’s no subsequent row for which to retrieve a value.

In both of the above examples I used the WINDOW clause to create a named window. This allowed me to refer to that window from various OVER clauses in the SELECT list. This saved me from having to replicate the same code multiple times.

Option 3: The `NTH_VALUE()` Function

The NTH_VALUE() function is similar to LAG() and LEAD(), except that it allows us to choose the row number of the value we want to return. This is an absolute row number, so it’s not relative to the current row like the other two functions are.

Example:

SELECT 
    IdiotName,
    IQ,
    NTH_VALUE( IQ, 3 ) OVER win AS "Nth Value",
    IQ - NTH_VALUE( IQ, 3 ) OVER win AS "Current Minus Nth",
    NTH_VALUE( IQ, 3 ) OVER win - IQ AS "Nth Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING );

Result:

+-------------------+------+-----------+-------------------+-------------------+
| IdiotName         | IQ   | Nth Value | Current Minus Nth | Nth Minus Current |
+-------------------+------+-----------+-------------------+-------------------+
| Dumbest           |   30 |        40 |               -10 |                10 |
| Homer             |   40 |        40 |                 0 |                 0 |
| Patrick Star      |   40 |        40 |                 0 |                 0 |
| Ed                |   40 |        40 |                 0 |                 0 |
| Dumber            |   50 |        40 |                10 |               -10 |
| Peter Griffin     |   50 |        40 |                10 |               -10 |
| Cosmo             |   55 |        40 |                15 |               -15 |
| Dumb              |   60 |        40 |                20 |               -20 |
| Ralph Wiggum      |   65 |        40 |                25 |               -25 |
| Richard Watterson |   70 |        40 |                30 |               -30 |
+-------------------+------+-----------+-------------------+-------------------+

Option 4: The `FIRST_VALUE()` Function

The FIRST_VALUE() function is similar to the above functions, except that it gets its value from the first row in the window frame. If there are no partitions, then this is the first row in the result set. Otherwise it’s the first row in the partition.

Example:

SELECT 
    IdiotName,
    IQ,
    FIRST_VALUE( IQ ) OVER win AS "First Value",
    IQ - FIRST_VALUE( IQ ) OVER win AS "Current Minus First",
    FIRST_VALUE( IQ ) OVER win - IQ AS "First Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );

Result:

+-------------------+------+-------------+---------------------+---------------------+
| IdiotName         | IQ   | First Value | Current Minus First | First Minus Current |
+-------------------+------+-------------+---------------------+---------------------+
| Dumbest           |   30 |          30 |                   0 |                   0 |
| Homer             |   40 |          30 |                  10 |                 -10 |
| Patrick Star      |   40 |          30 |                  10 |                 -10 |
| Ed                |   40 |          30 |                  10 |                 -10 |
| Dumber            |   50 |          30 |                  20 |                 -20 |
| Peter Griffin     |   50 |          30 |                  20 |                 -20 |
| Cosmo             |   55 |          30 |                  25 |                 -25 |
| Dumb              |   60 |          30 |                  30 |                 -30 |
| Ralph Wiggum      |   65 |          30 |                  35 |                 -35 |
| Richard Watterson |   70 |          30 |                  40 |                 -40 |
+-------------------+------+-------------+---------------------+---------------------+

Option 5: The `LAST_VALUE()` Function

The LAST_VALUE() function is similar to the FIRST_VALUE() function, except that it gets its value from the last row in the window frame.

Example:

SELECT 
    IdiotName,
    IQ,
    LAST_VALUE( IQ ) OVER win AS "Last Value",
    IQ - LAST_VALUE( IQ ) OVER win AS "Current Minus Last",
    LAST_VALUE( IQ ) OVER win - IQ AS "Last Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING );

Result:

+-------------------+------+------------+--------------------+--------------------+
| IdiotName         | IQ   | Last Value | Current Minus Last | Last Minus Current |
+-------------------+------+------------+--------------------+--------------------+
| Dumbest           |   30 |         70 |                -40 |                 40 |
| Homer             |   40 |         70 |                -30 |                 30 |
| Patrick Star      |   40 |         70 |                -30 |                 30 |
| Ed                |   40 |         70 |                -30 |                 30 |
| Dumber            |   50 |         70 |                -20 |                 20 |
| Peter Griffin     |   50 |         70 |                -20 |                 20 |
| Cosmo             |   55 |         70 |                -15 |                 15 |
| Dumb              |   60 |         70 |                -10 |                 10 |
| Ralph Wiggum      |   65 |         70 |                 -5 |                  5 |
| Richard Watterson |   70 |         70 |                  0 |                  0 |
+-------------------+------+------------+--------------------+--------------------+

Here I used ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to expand the range to the end of the result set. Omitting this produces the following result:

SELECT 
    IdiotName,
    IQ,
    LAST_VALUE( IQ ) OVER win AS "Last Value",
    IQ - LAST_VALUE( IQ ) OVER win AS "Current Minus Last",
    LAST_VALUE( IQ ) OVER win - IQ AS "Last Minus Current"
FROM Idiots
WINDOW win AS ( ORDER BY IQ );

Result:

+-------------------+------+------------+--------------------+--------------------+
| IdiotName         | IQ   | Last Value | Current Minus Last | Last Minus Current |
+-------------------+------+------------+--------------------+--------------------+
| Dumbest           |   30 |         30 |                  0 |                  0 |
| Homer             |   40 |         40 |                  0 |                  0 |
| Patrick Star      |   40 |         40 |                  0 |                  0 |
| Ed                |   40 |         40 |                  0 |                  0 |
| Dumber            |   50 |         50 |                  0 |                  0 |
| Peter Griffin     |   50 |         50 |                  0 |                  0 |
| Cosmo             |   55 |         55 |                  0 |                  0 |
| Dumb              |   60 |         60 |                  0 |                  0 |
| Ralph Wiggum      |   65 |         65 |                  0 |                  0 |
| Richard Watterson |   70 |         70 |                  0 |                  0 |
+-------------------+------+------------+--------------------+--------------------+

Option 1: The LAG() Function

Option 2: The LEAD() Function

Option 3: The NTH_VALUE() Function

Option 4: The FIRST_VALUE() Function

Option 5: The LAST_VALUE() Function

Option 1: The `LAG()` Function

Option 2: The `LEAD()` Function

Option 3: The `NTH_VALUE()` Function

Option 4: The `FIRST_VALUE()` Function

Option 5: The `LAST_VALUE()` Function