How to Subtract the Previous Row's Value in a Grouped MySQL Query?

MySQL: Subtract the value of the previous row in grouped query

Subtracting a value from the previous row is a common task in data analysis, but can present challenges when working with grouped data. MySQL provides a general method to solve this situation.

To calculate the difference in energy consumption between consecutive rows for each SN, we use MySQL's system variables (@lastSN and @lastValue) and a query that makes a single pass over the data:

<code class="language-sql">SELECT
  EL.SN,
  EL.Date,
  EL.Value,
  IF(@lastSN = EL.SN, EL.Value - @lastValue, 0.00) AS Consumption,
  @lastSN := EL.SN,
  @lastValue := EL.Value
FROM
  EnergyLog EL,
  (SELECT @lastSN := 0, @lastValue := 0) SQLVars
ORDER BY
  EL.SN, EL.Date;</code>

Queries work as follows:

1. Declare the MySQL variables @lastSN and @lastValue to track the previous SN and Value.
2. For each row in the EnergyLog table, compare the current SN to @lastSN.
3. If SN matches, use IF() to calculate the energy difference. Otherwise, set Consumption to 0.
4. After comparison, update @lastSN and @lastValue with the value of the current row.
5. Sort results by SN and Date to ensure correct energy consumption calculations.

By using MySQL variables, we implemented a simple and efficient solution for calculating energy consumption based on the previous value in a grouped query.

The above is the detailed content of How to Subtract the Previous Row's Value in a Grouped MySQL Query?. For more information, please follow other related articles on the PHP Chinese website!