CODEWITHSUNDEEP
mysql
lomse
lomse

Reputation: 4165

Group by day with filled gaps

I am trying to generate a daily sales reports for a particular user based on this tutorial Using MySQL to generate daily sales reports with filled gaps. To do this, I have three tables, records table, user table and calendar table

records     user      calendar
id          id        datefield
user_id     
timestamp

The query below returns 0 as total and NULL as the user_id when data is not available for a particular day which is great: 

SELECT calendar.datefield AS DATE,
       IFNULL(COUNT(records.id),0) AS total, records.user_id
FROM records 
RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield)    

WHERE ( 
    calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()
)
GROUP BY DATE DESC

The idea is to generate this report for a particular user so I modified the above query to what follows: 

SELECT calendar.datefield AS DATE,
           IFNULL(COUNT(records.id),0) AS total, records.user_id
    FROM records 
    RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield)

    WHERE ( 
        calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()
        AND records.user_id = SOME_EXISTING_USER_ID
    )
    GROUP BY DATE DESC

This return an empty result when there is no record but the idea is to return 0 for any particular day which does not have data.

How can I modify the first query to work for a particular user?

Upvotes: 1

Views: 1788

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

Wow. Been a while since I've seen a RIGHT JOIN in the wild! Anyway, try adding the user predicate from the WHERE clause into the RIGHT JOIN like this:

SELECT calendar.datefield AS DATE,
           IFNULL(COUNT(records.id),0) AS total, records.user_id
    FROM records 
    RIGHT JOIN calendar ON (DATE(records.timestamp) = calendar.datefield) 
                        AND records.user_id = SOME_EXISTING_USER_ID

    WHERE ( 
        calendar.datefield BETWEEN (NOW() - INTERVAL 14 DAY) AND NOW()
    )
    GROUP BY DATE DESC;

For me this is one of the great benefits of explicit joins vs implicit joins...

Upvotes: 5

Related Questions