CODEWITHSUNDEEP
mysqlsql
Martin James
Martin James

Reputation: 930

Return count for last 7 days with missing days filled in

I'm trying very hard to produce a dataset of total sales for each day in the last 7 days, with missing days filled in.

The result I'm looking for, for my example data, is:

2019-01-05  0
2019-01-06  0
2019-01-07  0
2019-01-08  0
2019-01-09  0
2019-01-10  1
2019-01-11  0

What I am getting is:

2019-01-05  0
2019-01-06  0
2019-01-07  0
2019-01-08  0
2019-01-09  0
2019-01-10  0
2019-01-11  0

The example data, albeit shortened for SO, looks like this, which is only one row:

id | saleID            | amount      | created
---|-------------------|-------------|-------------
1  | 6032b317-1533.... | 20000       | 2019-01-10 23:56:4

ID = INT 11 Auto
saleID = CHAR 36 INDEX
amount = INT 11 INDEX
created = DATETIME INDEX

My query looks like this at the moment:

SELECT
    qb.dy AS saleDay,
    COALESCE(COUNT(ets.saleID), 0) AS saleDayTotal
FROM eventTicketSales AS ets 
RIGHT JOIN (
    SELECT curdate() AS dy UNION
    SELECT DATE_SUB(curdate(), INTERVAL 1 DAY) AS dy UNION
    SELECT DATE_SUB(curdate(), INTERVAL 2 DAY) AS dy UNION
    SELECT DATE_SUB(curdate(), INTERVAL 3 DAY) AS dy UNION
    SELECT DATE_SUB(curdate(), INTERVAL 4 DAY) AS dy UNION
    SELECT DATE_SUB(curdate(), INTERVAL 5 DAY) AS dy UNION
    SELECT DATE_SUB(curdate(), INTERVAL 6 DAY) AS dy        
) AS qb ON ets.created = qb.dy AND ets.created > DATE_SUB(curdate(), INTERVAL 7 DAY)
GROUP BY qb.dy
ORDER BY qb.dy ASC;

You can play with a fiddle here.

Please tell me what I'm doing wrong and suggest how it could be fixed.

Thanks.

Upvotes: 1

Views: 49

Answers (1)

GMB
GMB

Reputation: 222512

... AS qb ON ets.created = qb.dy

You are comparing a datetime with a date without time. This does not match because the time is, obvisously, not the same.

To solve this you could convert the datetime to a date before comparing, using the DATE fuction : 

... AS qb ON DATE(ets.created) = qb.dy

Upvotes: 2

Related Questions