MySQL: Query to get number of log entries per week -- including empty sets

Question

My web application has a report that shows the number of logins by a particular user each week. However, I'm struggling to get the query just right. The problem I'm running into is that I can't seem to get the weeks during which the user did not login at all.

Currently my query looks like this:

SELECT
    DATE_ADD(logDate, INTERVAL(1-DAYOFWEEK(logDate)) DAY) weekStart,
    DATE_ADD(logDate, INTERVAL(7-DAYOFWEEK(logDate)) DAY) weekEnd,
    COUNT(*) loginCount
FROM log
WHERE
    logDate > $startDate AND
    logDate < $endDate

Answer 1

I would create a table for week numbers:

CREATE TEMPORARY TABLE weeks (weeknum INT PRIMARY KEY);

Then populate that table with integer values 0..53.

Then join it to your log table using the WEEK() function:

SELECT weeks.weeknum, COUNT(*) loginCount
FROM weeks LEFT OUTER JOIN log ON weeks.weeknum = WEEK(log.logDate)
WHERE log.logDate BETWEEN ? AND ?
GROUP BY weeks.weeknum;

If you need this query to support a date range that spans multiple years, use YEARWEEK() instead, and populate your temp table with more rows in the YYYYWW format like values returned by YEARWEEK().