title: MySQL get difference between max and min values for select rows
body: <p>I want a query that gets the difference between the max and min values grouped by day.  My attempt does not work:</p>
  
  <pre><code>SELECT date(`date`), 
     (max(value) - min(value)) as value,
  FROM `sub_meter_data` 
  where date(sub_meter_data.date) &gt; '2012-10-01' 
    and sub_meterID in('58984','58985','58986','58987')
  group by date(`date`);
  </code></pre>
  
  <p>Each sub_meter has a value that might be >3000, but only differs by &lt; 10 per day.  I want the difference, ie a result &lt;10.  With the query above I get results >3000.</p>
  
  <p>This query below, just selects one meter, and give the correct results, the max (17531), the min (17523), and the difference (8).</p>
  
  <pre><code>SELECT date(sub_meter_data.date) as date,
     max(value) as max_meter,
     min(value) as min_meter, 
     max(value) - min(value) as diff,
  FROM `sub_meter_data`
  where date(sub_meter_data.date) &gt; '2012-10-01' 
    and sub_meterID in('57636')
    group by date(sub_meter_data.date)
  </code></pre>
  
  <p>But adding another meter into the in clause, give a bad result, the max is 17531, and the min is 3021, the diff is 14510.  But I want the diff for each meter, then summed together.</p>
  
  <pre><code>SELECT date(sub_meter_data.date) as date,
     max(value) as max_meter,
     min(value) as min_meter, 
     max(value) - min(value) as diff,
     FROM `sub_meter_data`
  where date(sub_meter_data.date) &gt; '2012-10-01' 
    and sub_meterID in('57636', '57628')
    group by date(sub_meter_data.date)
  </code></pre>
  
  <p>Another attempt I've tried is:</p>
  
  <pre><code>SELECT date(sub_meter_data.date) as date,
     sum(CASE WHEN sub_meterID = '57628' OR sub_meterID = '57636' THEN (max(value) -     min(value)) ELSE 0 END) as value
     FROM `sub_meter_data`
  where date(sub_meter_data.date) &gt; '2012-10-01' 
  </code></pre>