title: SQL Average of fields based on conditions
body: <p>I am creating a reporting tool and it has a specific requirement I would like to solve in the SQL world. I am using MySQL 5.6.11:
  We have some inputs that can be introduced none, one or twice a day:</p>
  
  <pre><code>DATETIME            | VALUE
  
  01-01-2015 00:10:11 |   5 
  01-03-2015 00:11:11 |   3
  01-03-2015 00:12:11 |   4
  01-03-2015 00:11:11 |   5
  01-03-2015 00:12:11 |   8
  </code></pre>
  
  <p>I would like to get the average of the values the following way:</p>
  
  <pre><code>DATETIME   | FIRST INSERTED VALUE AVERAGE | SECOND INSERTED VALUE AVERAGE
  01-01-2015 |          5                   | null
  01-03-2015 |          4                   | 6
  </code></pre>
  
  <p>We are interested on having the average of all the records, but the time is important.
  We want the average of the first value introduced that specific day for all the reports in a column and the average of the second introduced values (if any) on the second.</p>
  
  <p>This is an example of one report without considering the difference between the first and the second value</p>
  
  <pre><code>SELECT AVG(bt.value), DATE(bt.date) 
      FROM reports bt
      GROUP BY DATE(bt.date) 
  </code></pre>