Count sum of sale from last 7 weekdays

Question

I have a transaction table and I want to sum the total of daily sales and display it on a graph related to the past 7 days.

SELECT weekday(date), sum(amount) FROM m1pay_dashboard.transactions where date > (DATE(NOW()) - INTERVAL 7 DAY)
group by weekday(date)

This query will display the output below:

|   | weekday     | sale  |
| - | ----------  | ----- |
| 1 | Monday      | 20    |
| 2 | Tuesday     | 50    |
| 3 | Friday      | 120   |
| 4 | Saturday    | 140   |
| 5 | Sunday      | 110   |

but I need to have the sale regarding all 7 days. (displaying zero for days which does not exist) here is the sample output which I need:

|   | weekday     | sale  |
| - | ----------  | ----- |
| 1 | Monday      | 20    |
| 2 | Tuesday     | 50    |
| 3 | Wednesday   | 0     |
| 4 | Thursday    | 0     |
| 5 | Friday      | 120   |
| 6 | Saturday    | 140   |
| 7 | Sunday      | 110   |

What do you get instead? What is the problem with the query you have? What is your actual question? — mkrieger1, Apr 22 '20 at 06:40
Consider handling issues of data display in application code — Strawberry, Apr 22 '20 at 06:42
I would point out that your query only selects two columns but your claimed output contains 3 (unless that first column is a line number of the row). It is confusing because you use WEEKDAY which returns number (like the first column) but you seem to claim it returns name (like the second column) — Caius Jard, Apr 22 '20 at 07:40

Caius Jard · Accepted Answer · 2020-04-22T06:57:20.393

2

One simple solution is to union on a load of zero records(only 7, not onerous) like:

SELECT d, sum(amount) 
FROM 
(
  SELECT dayofweek(date) as d, amount 
  FROM m1pay_dashboard.transactions 
  where date > (DATE(NOW()) - INTERVAL 7 DAY)
  UNION ALL
  SELECT 1, 0
  UNION ALL 
  SELECT 2, 0
  ...
  UNION ALL 
  SELECT 7, 0
) x
group by d

Being zero they won't participate in sums but provide a zero for those days that lack sales

If you want the day name in there too, add another column to the top query and a string to all the unions, but be mindful of any internationalization/localization obligations: it wouldn't be wise to hard code English words in if you will one day run the report on a French platform - better to leave it as a day number only and have the front end make it a name depending on the translation, even whether the target country considers day 1 a Sunday or a Monday

edited Apr 22 '20 at 06:57

answered Apr 22 '20 at 06:48

Caius Jard

72,509
5
49
80

Thanks but your answer rise exception ```Every derived table must have its own alias``` – user435245 Apr 22 '20 at 06:56
Add an alias after the closing bracket - it was a typo in the answer (i've since added an x). To be ultra clear, you also need to replace the `...` with a pattern of `UNION ALL SELECT , 0` before this query will run – Caius Jard Apr 22 '20 at 06:57
another thing to mention is that since weekdays start from 0 in MySQL; I think we should start the UNION statement from 0 index till 6. Am I right? – user435245 Apr 22 '20 at 07:02
DAYOFWEEK runs 1-7, WEEKDAY runs 0-6, my query uses DOW - pick your poison! https://stackoverflow.com/questions/47589759/mysql-weekday-vs-mysql-dayofweek – Caius Jard Apr 22 '20 at 07:37
1

important to note that each function also assigns a different number to monday etc, if you're translating `1-> a day name` be mindful of which function you used – Caius Jard Apr 22 '20 at 07:52

Nick · Answer 2 · 2020-04-22T07:15:50.823

1

One way to do this is to use a numbers table to generate the number of days prior to now that you are interested in, and then LEFT JOIN that to the transactions table:

SELECT WEEKDAY(CURDATE() - INTERVAL days.n DAY) AS weekday, COALESCE(SUM(t.amount), 0) AS total
FROM (SELECT 0 AS n UNION ALL SELECT 1 
      UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5
      UNION ALL SELECT 6) days
LEFT JOIN m1pay_dashboard.transactions t ON t.`date` = CURDATE() - INTERVAL days.n DAY
GROUP BY weekday

Demo (with a fixed date in place of CURDATE()) on dbfiddle

edited Apr 22 '20 at 07:15

answered Apr 22 '20 at 07:01

Nick

138,499
22
57
95

I considered this, but then came to the conclusion that because row generation is such a pain in the arse in MySQL, if we were going to generate 7 rows with union then left join to it, we might as well just generate 7 rows onto the end of the results and sum them! Good technique for other queries, particularly where hundreds or thousands of rows are needed, and union'd blocks can be cross joined to generate N rows – Caius Jard Apr 22 '20 at 07:38
@CaiusJard I think this is more generic, yours is specific to this situation - but a very good one for it. – Nick Apr 22 '20 at 07:41

Count sum of sale from last 7 weekdays

2 Answers2