mysql subtract two rows from column and places into an alias

coalesce, mysql, sql, subtraction

Solution

Just give the table name an alias, and the table inside the correlated subquery a different alias name. Something like this:

SELECT 
  m1.id, 
  m1.kwh,
  COALESCE(m1.kwh - (SELECT m2.kwh 
                     FROM meter_readings AS m2
                     WHERE m2.id = m1.id + 1),
           m1.kwh) AS consumption 
FROM meter_readings AS m1;

SQL Fiddle Demo

This will give you:

| ID |     KWH | CONSUMPTION |
------------------------------
|  1 | 4567.89 |     1141.18 |
|  2 | 3426.71 |     1181.37 |
|  3 | 2245.34 |     2245.34 |

Update 1

For the updated sample data, just use `WHERE m2.id = m1.id - 1` inside the correlated subquery with `COALESCE(..., 0)` so that the first one will be 0. Like this:

SELECT 
  m1.id, 
  date_format(m1.date_taken, '%Y-%m-%d') AS date_taken,
  m1.kwh,
  COALESCE(m1.kwh - (SELECT m2.kwh 
                     FROM meter_readings m2
                     WHERE m2.id = m1.id - 1), 0) AS consumption 
FROM meter_readings m1;

Updated SQL Fiddle Demo

This will give you:

| ID | DATE_TAKEN |     KWH | CONSUMPTION |
-------------------------------------------
|  1 | 2013-01-01 | 4567.89 |           0 |
|  2 | 2013-01-08 | 4596.71 |       28.82 |
|  3 | 2013-01-15 | 4607.89 |       11.18 |

Problem

I have table `meter_readings` with columns: `id`, `date_taken`, `kwh`. I'm trying to subtract two rows in `kwh` column together and put the results into an alias called `consumption`. I'm using: ``` SELECT id, kwh COALESCE(kwh-(SELECT kwh FROM meter_readings WHERE id= id+1), kwh) AS consumption FROM meter_readings; ``` What I get back in consumption alias is simple the same as the original `kwh`: ``` id date_taken kwh consumption 1 2013-01-01 4567.89 4567.89 2 2013-01-08 4596.71 4596.71 3 2013-01-15 4607.89 4607.89 ``` what I would like is: ``` id date_taken kwh consumption 1 2013-01-01 4567.89 0 2 2013-01-08 4596.71 28.11 3 2013-01-15 4607.89 11.18 ``` so id 1 = 0 because this is the first date_taken kwh reading so has no need for a consumption value. This is trying to calculate over a year the weekly kwh consumption.

Original source