title: How to subtract two MySQL SELECT statements
body: <p>I have two mysql select statements and I want to subtract one from another:</p>
  
  <p>First Select Statement:</p>
  
  <pre><code>SELECT rec.time_stamp,
         rec.day_chan1,
         rec.day_chan2,
         rec.day_chan3,
         rec.total_phase
  FROM
    (SELECT ".$this-&gt;table_name.".*,
            DATE(time_stamp) AS mydate,
            TIME(time_stamp) AS mytime
     FROM ".$this-&gt;table_name."
     WHERE TIME(time_stamp) &gt;= '00:00:00'
       AND TIME(time_stamp) &lt;= '07:59:59') AS rec
  JOIN
    (SELECT DATE(time_stamp) AS mydate,
            MAX(TIME(time_stamp)) AS mytime
     FROM ".$this-&gt;table_name."
     WHERE TIME(time_stamp) &gt;= '00:00:00'
       AND TIME(time_stamp) &lt;= '07:59:59'
     GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
  AND max_times.mytime = rec.mytime
  ORDER BY rec.mydate
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>+---------------------+-----------+-----------+-----------+-------------+
  | time_stamp          | day_chan1 | day_chan2 | day_chan3 | total_phase |
  +---------------------+-----------+-----------+-----------+-------------+
  | 2014-06-03 07:59:48 |     24210 |     22744 |     26003 |       72957 |
  | 2014-06-04 07:59:49 |    112603 |    103417 |    121368 |      337388 |
  | 2014-06-05 07:59:50 |     21302 |     20165 |     23317 |       64784 |
  | 2014-06-06 07:59:50 |     21561 |     20951 |     23875 |       66387 |
  | 2014-06-07 07:59:03 |       408 |      1151 |       767 |        2326 |
  | 2014-06-08 07:59:04 |       384 |      1151 |       767 |        2302 |
  | 2014-06-09 07:59:53 |     21580 |     20656 |     23788 |       66024 |
  | 2014-06-10 07:59:54 |     19689 |     18861 |     21657 |       60207 |
  | 2014-06-11 07:59:54 |     22283 |     21132 |     24820 |       68235 |
  | 2014-06-12 07:59:56 |     20489 |     19327 |     22377 |       62193 |
  | 2014-06-13 07:59:56 |     20646 |     19485 |     22504 |       62635 |
  | 2014-06-15 07:59:09 |       387 |      1151 |       767 |        2305 |
  | 2014-06-16 07:59:58 |     30681 |     29050 |     33593 |       93324 |
  | 2014-06-17 07:59:59 |     18248 |     17701 |     20432 |       56381 |
  | 2014-06-18 07:37:49 |     40170 |     38123 |     47013 |      125306 |
  | 2014-06-19 07:59:38 |     22417 |     21001 |     24484 |       67902 |
  | 2014-06-20 07:59:50 |     19104 |     18238 |     21007 |       58349 |
  | 2014-06-21 07:59:15 |       384 |      1151 |       767 |        2302 |
  | 2014-06-22 07:59:16 |       384 |      1151 |       767 |        2302 |
  | 2014-06-23 07:59:53 |     19675 |     18582 |     21114 |       59371 |
  | 2014-06-24 07:59:42 |     18341 |     17504 |     19427 |       55272 |
  | 2014-06-25 07:59:55 |     19554 |     18469 |     20879 |       58902 |
  | 2014-06-26 07:59:55 |     20241 |     19262 |     21881 |       61384 |
  | 2014-06-27 07:59:56 |     17984 |     17033 |     19528 |       54545 |
  | 2014-06-28 07:59:21 |       394 |      1151 |       767 |        2312 |
  | 2014-06-29 07:59:22 |       406 |      1151 |       767 |        2324 |
  | 2014-06-30 07:59:58 |     18909 |     18012 |     20306 |       57227 |
  | 2014-07-01 07:59:59 |     17736 |     16951 |     19213 |       53900 |
  | 2014-07-02 07:59:48 |     19088 |     18310 |     21040 |       58438 |
  | 2014-07-03 07:59:49 |     19842 |     19024 |     21490 |       60356 |
  +---------------------+-----------+-----------+-----------+-------------+
  </code></pre>
  
  <p>Second Select Statement:</p>
  
  <pre><code>SELECT rec.time_stamp,
         rec.day_chan1,
         rec.day_chan2,
         rec.day_chan3,
         rec.total_phase
  FROM
    (SELECT ".$this-&gt;table_name.".*,
            DATE(time_stamp) AS mydate,
            TIME(time_stamp) AS mytime
     FROM ".$this-&gt;table_name."
     WHERE TIME(time_stamp) &gt;= '00:00:00'
       AND TIME(time_stamp) &lt;= '07:59:59') AS rec
  JOIN
    (SELECT DATE(time_stamp) AS mydate,
            MAX(TIME(time_stamp)) AS mytime
     FROM ".$this-&gt;table_name."
     WHERE TIME(time_stamp) &gt;= '00:00:00'
       AND TIME(time_stamp) &lt;= '07:59:59'
     GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
  AND max_times.mytime = rec.mytime
  ORDER BY rec.mydate
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code>+---------------------+-----------+-----------+-----------+-------------+
  | time_stamp          | day_chan1 | day_chan2 | day_chan3 | total_phase |
  +---------------------+-----------+-----------+-----------+-------------+
  | 2014-06-03 07:59:48 |     24210 |     22744 |     26003 |       72957 |
  | 2014-06-04 07:59:49 |    112603 |    103417 |    121368 |      337388 |
  | 2014-06-05 07:59:50 |     21302 |     20165 |     23317 |       64784 |
  | 2014-06-06 07:59:50 |     21561 |     20951 |     23875 |       66387 |
  | 2014-06-07 07:59:03 |       408 |      1151 |       767 |        2326 |
  | 2014-06-08 07:59:04 |       384 |      1151 |       767 |        2302 |
  | 2014-06-09 07:59:53 |     21580 |     20656 |     23788 |       66024 |
  | 2014-06-10 07:59:54 |     19689 |     18861 |     21657 |       60207 |
  | 2014-06-11 07:59:54 |     22283 |     21132 |     24820 |       68235 |
  | 2014-06-12 07:59:56 |     20489 |     19327 |     22377 |       62193 |
  | 2014-06-13 07:59:56 |     20646 |     19485 |     22504 |       62635 |
  | 2014-06-15 07:59:09 |       387 |      1151 |       767 |        2305 |
  | 2014-06-16 07:59:58 |     30681 |     29050 |     33593 |       93324 |
  | 2014-06-17 07:59:59 |     18248 |     17701 |     20432 |       56381 |
  | 2014-06-18 07:37:49 |     40170 |     38123 |     47013 |      125306 |
  | 2014-06-19 07:59:38 |     22417 |     21001 |     24484 |       67902 |
  | 2014-06-20 07:59:50 |     19104 |     18238 |     21007 |       58349 |
  | 2014-06-21 07:59:15 |       384 |      1151 |       767 |        2302 |
  | 2014-06-22 07:59:16 |       384 |      1151 |       767 |        2302 |
  | 2014-06-23 07:59:53 |     19675 |     18582 |     21114 |       59371 |
  | 2014-06-24 07:59:42 |     18341 |     17504 |     19427 |       55272 |
  | 2014-06-25 07:59:55 |     19554 |     18469 |     20879 |       58902 |
  | 2014-06-26 07:59:55 |     20241 |     19262 |     21881 |       61384 |
  | 2014-06-27 07:59:56 |     17984 |     17033 |     19528 |       54545 |
  | 2014-06-28 07:59:21 |       394 |      1151 |       767 |        2312 |
  | 2014-06-29 07:59:22 |       406 |      1151 |       767 |        2324 |
  | 2014-06-30 07:59:58 |     18909 |     18012 |     20306 |       57227 |
  | 2014-07-01 07:59:59 |     17736 |     16951 |     19213 |       53900 |
  | 2014-07-02 07:59:48 |     19088 |     18310 |     21040 |       58438 |
  | 2014-07-03 07:59:49 |     19842 |     19024 |     21490 |       60356 |
  +---------------------+-----------+-----------+-----------+-------------+
  </code></pre>
  
  <p>now I need to subtract first - second statement.</p>
  
  <p>I tried this but it did not work:</p>
  
  <pre><code>SELECT *
  FROM (
          (SELECT rec.time_stamp,
                  rec.day_chan1,
                  rec.day_chan2,
                  rec.day_chan3,
                  rec.total_phase
           FROM
             (SELECT ".$this-&gt;table_name.".*,
                     DATE(time_stamp) AS mydate,
                                         TIME(time_stamp) AS mytime
              FROM ".$this-&gt;table_name."
              WHERE TIME(time_stamp) &gt;= '08:00:00'
                AND TIME(time_stamp) &lt;= '16:59:59') AS rec
           JOIN
             (SELECT DATE(time_stamp) AS mydate,
                                         MAX(TIME(time_stamp)) AS mytime
              FROM ".$this-&gt;table_name."
              WHERE TIME(time_stamp) &gt;= '08:00:00'
                AND TIME(time_stamp) &lt;= '16:59:59'
              GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
           AND max_times.mytime = rec.mytime
           ORDER BY rec.mydate) -
          (SELECT rec.time_stamp,
                  rec.day_chan1,
                  rec.day_chan2,
                  rec.day_chan3,
                  rec.total_phase
           FROM
             (SELECT ".$this-&gt;table_name.".*,
                     DATE(time_stamp) AS mydate,
                                         TIME(time_stamp) AS mytime
              FROM ".$this-&gt;table_name."
              WHERE TIME(time_stamp) &gt;= '00:00:00'
                AND TIME(time_stamp) &lt;= '07:59:59') AS rec
           JOIN
             (SELECT DATE(time_stamp) AS mydate,
                                         MAX(TIME(time_stamp)) AS mytime
              FROM ".$this-&gt;table_name."
              WHERE TIME(time_stamp) &gt;= '00:00:00'
                AND TIME(time_stamp) &lt;= '07:59:59'
              GROUP BY DATE(time_stamp)) AS max_times ON max_times.mydate = rec.mydate
           AND max_times.mytime = rec.mytime
           ORDER BY rec.mydate))
  </code></pre>