SQL - Find Highest Single Day of Sales From Date Range

Question

I am using MySQL..

I have a simple sales table as follow:

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

And sale product table

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

Expected Result

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

How to achieve my expected result? I have tried as follow but it didn't work as expected:

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

Answer 1

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 total DESC
) AS result_for_highest_single_day
GROUP BY  store_id

I just have modified the script ORDER BY DATE(s.logDate) ASC >> ORDER BY total DESC and GROUP BY highestSingleDateOn, store_id >> GROUP BY store_id.

*Above sql script,it uses the unstable features about group by of MYSQL.

*Then according to Mysql standard,I write a other version sql script.

select table1.*
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.sale_id
   GROUP BY DATE(s.logDate), s.store_id) as table1
,
(select tmp.store_id,MAX(tmp.total) as max_total 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.sale_id
   GROUP BY DATE(s.logDate), s.store_id ) as tmp group by tmp.store_id) as table2

where table1.store_id = table2.store_id and table1.total=table2.max_total