Best day of the week

Question

I have a table with three columns

order;
orderID | ordertotal | datetime

1       | 100.00     | 2015-03-01 17:48:14
2       | 150.00     | 2015-05-10 02:33:20
3       | 5982.23    | 2015-06-11 17:11:30
...

I am trying to find entries for SUM ordertotals for days of the week.

In other words, I want to see the best Monday (for the amount of ordertotals), the best Tuesday and others.

Finally, I will get the result as shown below for seven days of the week.

 DailyTotal    | DayOfTheWeek | ActualDate

     7500      | Monday       | 2015-03-01
     8000      | Tuesday      | 2015-05-10
     5000      | Wednesday    | 2015-06-11
...

I tried this query and it worked, except for ActualDate;

SELECT
    MAX(o2.dailytotal) AS DailyTotal,
    DATE_FORMAT(o2.datetime2, '%W') AS DayOfTheWeek,
    o2.datetime2 AS ActualDate
FROM
    (
        SELECT
            SUM(s1.ordertotal) AS dailytotal,
            DATE(s1.datetime) AS datetime2
        FROM
            order AS o1
        GROUP BY
            DATE(o1.datetime)
    ) AS o2
GROUP BY DATE_FORMAT(o2.datetime2, '%W')
ORDER BY 1 DESC

I am waiting for your decisions.

Answer 1

This is the case where the trick substring_index()/ group_concat()is probably the best way to express a request:

SELECT MAX(o2.dailytotal) AS DailyTotal,
       DATE_FORMAT(o2.datetime2, '%W') AS DayOfTheWeek,
       SUBSTRING_INDEX(GROUP_CONCAT(o2.datetime2 ORDER BY o2.dailytotal DESC), ',', 1) AS ActualDate
FROM (SELECT DATE(s1.datetime) AS datetime2,
             SUM(s1.ordertotal) AS dailytotal      
      FROM `order` o1
      GROUP BY DATE(o1.datetime)
     ) o2
GROUP BY DATE_FORMAT(o2.datetime2, '%W')
ORDER BY DailyTotal DESC;

(, , group_concat() , - ). .

.