CODEWITHSUNDEEP
mysqlsql
StudioTime
StudioTime

Reputation: 23979

Show values even if empty

I am using the following to show a count of products added over the last 7 days...Can i somehow tailor the query to show all the last 7 days even if COUNT=0?

query as it stands:

SELECT DAYNAME(dateadded) DAY, COUNT(*) COUNT 
FROM `products` 
WHERE (`dateadded` BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE() && site_url = 'mysite.com') 
GROUP BY DAY(dateadded)

Upvotes: 3

Views: 164

Answers (1)

lc.
lc.

Reputation: 116458

Add a table with dates in it (a dates lookup table), then:

SELECT DAYNAME(d.FullDate) DAY, COUNT(*) COUNT
FROM dates d
LEFT OUTER JOIN products p ON d.FullDate = DATE(p.dateadded) 
    AND p.site_url = 'mysite.com'
WHERE d.FullDate BETWEEN DATE_SUB(CURDATE(), INTERVAL 7 DAY) AND CURDATE()    
GROUP BY d.FullDate

It takes a little bit of storage, yes, but it will make queries like this a lot easier.

Alternatively, you can make a stored procedure that loops through dates between 7 days ago and today and returns one row for each.

Upvotes: 1

Related Questions