title: SQL - Find Highest Single Day of Sales From Date Range
body: <p>I am using <strong>MySQL</strong>..</p>
  
  <p>I have a simple sales table as follow:</p>
  
  <pre><code>o----o----------o-----------o
  | id | store_id |  logDate  |
  o----o----------o-----------o
  | 1  |    1     | 2015-1-13 |
  | 2  |    1     | 2015-1-14 |
  | 3  |    2     | 2015-1-11 |
  | 4  |    2     | 2015-1-18 |
  o----o----------o-----------o
  </code></pre>
  
  <p>And sale product table</p>
  
  <pre><code>o----o----------o---------o------------o
  | id | sale_id  |  qty    |    price   |
  o----o----------o---------o------------o
  | 1  |    1     |       1 |         10 |
  | 2  |    2     |       1 |         10 |
  | 3  |    2     |       1 |         10 |
  | 4  |    3     |       1 |         10 |
  | 5  |    3     |       1 |         10 |
  | 6  |    3     |       1 |         10 |
  | 7  |    4     |       1 |         10 |
  | 8  |    4     |       1 |         10 |
  o----o----------o---------o------------o
  </code></pre>
  
  <p>Expected Result</p>
  
  <pre><code>o-- --------o----------------o---------------------o
  | store_id  | SUM(price*qty) |  Highest Date On    | 
  o-----------o----------------o---------------------o
  |     1     |             20 |           2015-1-14 |
  |     2     |             30 |           2015-1-11 |
  O-----------o----------------o---------------------o
  </code></pre>
  
  <p>How to achieve my expected result?
  I have tried as follow but it didn't work as expected:</p>
  
  <pre><code>SELECT store_id, MAX(total), highestSingleDateOn 
  FROM (
     SELECT SUM(price * qty) AS total, 
        DATE(s.logDate) AS highestSingleDateOn, s.store_id AS store_id
     FROM sale_product sp JOIN sales s ON s.id = sp.sales_id
     GROUP BY DATE(s.logDate), s.store_id
     ORDER BY DATE(s.logDate) ASC
  ) AS result_for_highest_single_day
  GROUP BY highestSingleDateOn, store_id
  </code></pre>