Getting Data For Every Single Day In Sql

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 B

Solution 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
        LEFTJOIN
        (
            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')
GROUPBY DAYOFMONTH(c.day)
ORDERBY DAYOFMONTH(c.day)

**UPDATE by Thorpe Obazee

SELECT  c.day,
        COUNT(site_id)
FROM    calendar c
        LEFTJOIN
        (
            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')
GROUPBY c.day
ORDERBY 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.

Solution 2:

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

Solution 3:

Another suggestion to answer this would be to just move the

WHEREsite_id=16

into the JOIN, for example,

SELECT
 c.[Day]
,COUNT(v.[id])
FROM TestDB.dbo.Calendar c
LEFTJOIN TestDB.dbo.Visitor v
ON c.[Day] = DATEPART(d,v.Created)
AND v.[SiteID] =16GROUPBY c.[Day]
ORDERBY 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.