CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Fatih Aytekin
Fatih Aytekin

Reputation: 300

Best day of the week

I have 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 the records for SUM of ordertotals for the days of the week.

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

Finally I will have a result like 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'm waiting for your solutions.

Upvotes: 4

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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

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;

There are issues with this (such as converting the date to a string and the possibility of the intermediate group_concat() string not being big enough -- which can be adjusted). However, the alternatives result in much more complex queries.

Your version just returns an indeterminate date.

Upvotes: 3

Related Questions