2

My sale Table

ID amount  created_at
48  10     2018-10-15 10:57:24
49  20     2018-10-16 10:58:14
50  25     2018-10-22 14:07:31
51  24     2018-10-24 12:13:15
52  36     2018-10-24 12:13:21
53  40     2018-10-30 09:46:37
54  40     2018-10-28 09:46:37
55  40     2018-11-1 09:46:37
56  40     2018-11-2 09:46:37
57  40     2018-11-2 09:46:37
58  40     2018-11-2 09:46:37
59  40     2018-11-2 09:46:37
60  40     2018-11-2 09:46:37

My qyery 

SELECT Date(created_at),
       Count(*)
FROM   sale
WHERE  Date(created_at) BETWEEN ( Now() - INTERVAL 7 day ) AND Now()
GROUP  BY Date(created_at)

My result 

date(created_at)       count
2018-10-28 12:13:15    1
2018-10-1 09:46:37     1
2018-10-2 09:46:37     5

Suppose Week Start from 2018-10-28 and i need result like below if there is no record of particular day then it will 0.

day count
mon 1
tue 0
wed 0  
thu 1
fri 5
sat 0
sun 0
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
Dhruv Raval
  • 1,535
  • 1
  • 8
  • 15

1 Answers1

2
  • Firstly, you can create a Master Table representing all the Abbreviated Weekday name(s).
  • Now, we can use Date_format() function with %a specifier to get the abbreviated weekday name for a particular created_at date. We can use this as our Left Join condition.
  • Left Join allows us to consider all the Weekdays, even if there is no matching created_at for a particular day.
  • Count(*) will not work here, as it counts all the rows in a group. However, we don't want to count the rows where there is no matching rows. So, we use Count(created_at), as it will ignore the null values.
  • Finally, query is made sargable by removing Date() function usage in the Where clause.

You will need to use a master table for week days.

Query:

SELECT week_table.day_order, 
       week_table.wkday AS `day`, 
       Count(created_at) AS `count` 
FROM   
(
 SELECT 'Mon' AS wkday, 1 AS day_order UNION ALL
 SELECT 'Tue', 2 UNION ALL
 SELECT 'Wed', 3 UNION ALL
 SELECT 'Thu', 4 UNION ALL
 SELECT 'Fri', 5 UNION ALL
 SELECT 'Sat', 6 UNION ALL
 SELECT 'Sun', 7
) AS week_table 
LEFT JOIN sale AS s 
  ON DATE_FORMAT(created_at, '%a') = week_table.wkday AND 
     created_at >= ( CURDATE() - INTERVAL 7 day ) AND 
     created_at <  ( CURDATE() + INTERVAL 1 day )
GROUP BY week_table.day_order, week_table.wkday 
ORDER BY week_table.day_order;

Result 

| day_order | day | count |
| --------- | --- | ----- |
| 1         | Mon | 0     |
| 2         | Tue | 1     |
| 3         | Wed | 0     |
| 4         | Thu | 1     |
| 5         | Fri | 5     |
| 6         | Sat | 0     |
| 7         | Sun | 1     |

View on DB Fiddle

Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57