title: mysql subtract two rows from column and places into an alias
body: <p>I have table <strong><code>meter_readings</code></strong> with columns: <code>id</code>, <code>date_taken</code>, <code>kwh</code>.</p>
  
  <p>I'm trying to subtract two rows in <code>kwh</code> column together and put the results into an alias called <code>consumption</code>.</p>
  
  <p>I'm using:</p>
  
  <pre><code>SELECT id, kwh COALESCE(kwh-(SELECT kwh FROM meter_readings WHERE id= id+1), kwh) AS consumption 
  FROM meter_readings; 
  </code></pre>
  
  <p>What I get back in consumption alias is simple the same as the original <code>kwh</code>:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>what I would like is:</p>
  
  <pre><code> 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
  </code></pre>
  
  <p>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. </p>