Getting data for every single day in SQL

Question

I currently have the following SQL statement

MySQL Query:

SELECT
    c.day,
    COUNT(*)
FROM
    calendar c
JOIN
    visitors d
ON  DAYOFMONTH(d.created) = c.day
WHERE
    c.day BETWEEN DAYOFMONTH('2012-10-01') AND DAYOFMONTH('2012-10-31')
AND
    site_id = 16
GROUP BY
    DAYOFMONTH(d.created)
ORDER BY
    DAYOFMONTH(d.created)

My Tables:

Calendar

id  | day
---------
1   | 1
2   | 2
3   | 3
...
31  | 31

Visitors

id  | site_id | created
-----------------------------------
1   | 16      | 2012-10-18 11:14:39
2   | 16      | 2012-10-18 11:15:17
3   | 11      | 2012-10-18 11:49:14
4   | 11      | 2012-10-18 11:49:43
5   | 16      | 2012-10-19 11:54:37
6   | 1       | 2012-10-19 05:56:31
7   | 2       | 2012-10-19 05:57:56

I have created the table, calendar as prescribed in this answer but I seem to still get the same information. I am only getting the dates where I have data.

day    |   COUNT(*)
---------------------
18     |   2
19     |   1

I need to also retrieve 0 on the dates that have no data.

UPDATE:

I tried this:
SELECT *
FROM calendar c
LEFT JOIN visitors d
ON  DAYOFMONTH(d.created) = c.day

and

SELECT *
FROM calendar c
LEFT JOIN visitors d
ON  DAYOFMONTH(d.created) = c.day
WHERE  site_id = 16

I can confirm that the site_id = 16 is certainly the one killing the results.

Answer 1

use LEFT JOIN instead of INNER JOIN

SELECT ...
FROM   calendar c 
       LEFT JOIN  visitors d
         ON DAYOFMONTH(d.created) = c.day
WHERE...

INNER JOIN retrieves only rows which has atleast one match on the other table while LEFT JOIN retrieves all rows define on the lefthand side table whether it has a match or none on the other table(s).

UPDATE 1

SELECT  c.day,
        COUNT(*)
FROM    calendar c
        LEFT JOIN
        (
            SELECT *
            FROM   visitors
            WHERE  site_id = 16
        ) d ON  DAYOFMONTH(d.created) = c.day
WHERE c.day BETWEEN DAYOFMONTH('2012-10-01') AND DAYOFMONTH('2012-10-31')
GROUP BY DAYOFMONTH(c.day)
ORDER BY DAYOFMONTH(c.day)

**UPDATE by Thorpe Obazee

SELECT  c.day,
        COUNT(site_id)
FROM    calendar c
        LEFT JOIN
        (
            SELECT *
            FROM   visitors
            WHERE  site_id = 16
        ) d ON  DAYOFMONTH(d.created) = c.day
WHERE c.day BETWEEN DAYOFMONTH('2012-10-01') AND DAYOFMONTH('2012-10-31')
GROUP BY c.day
ORDER BY c.day

We cannot use COUNT(*) since it will return 1 every day. We also should not use DAYOFMONTH on c.day in the GROUP BY and ORDER BY since it is already what we need.

Answer 2

Another suggestion to answer this would be to just move the

WHERE site_id = 16

into the JOIN, for example,

SELECT
 c.[Day]
,COUNT(v.[id])
FROM TestDB.dbo.Calendar c
LEFT JOIN TestDB.dbo.Visitor v
ON c.[Day] = DATEPART(d,v.Created)
AND v.[SiteID] = 16
GROUP BY c.[Day]
ORDER BY c.[Day]

I personally don't like using derived tables as I have seen much better performance using a more native join, however, I normally work on datasets with millions of rows.

Answer 3

As the last poster said use LEFT JOIN but for Clarification.

Imagine your Calendar table is the LEFT table and the Visitors table is the right table

With INNER JOIN

FROM Calendar c
INNER JOIN Visitors v
ON c.Date = v.Date

There MUST be a match on both the LEFT and RIGHT side.

With LEFT JOIN

FROM Calendar c
LEFT JOIN Visitors v
ON c.Date = v.Date

You will return ALL records from your LEFT table (Calendar) and only records that match from your RIGHT table (Visitors) you will be left with NULL for all the Calendar dates that do not have any matching Visitor data (unless of course your handle this with somthing like COALESCE)

With RIGHT JOIN

FROM Calendar c
RIGHT JOIN Visitors v
ON c.Date = v.Date

You will return ALL records from your RIGHT table (Visitor) and only records that match from your LEFT table (Calendar) again you will have NULLS for column values where no match was found, this would catch any records you have Visitor data for, even if the date was not in the Calendar table.

Hope that helps.

Kevin