CODEWITHSUNDEEP
mysqlsqldatabase
Ryan Mortier
Ryan Mortier

Reputation: 873

MySQL count records for the past 7 days grouped by and with zero records

For the past two days I've been trying to figure this out. This is something that I thought would be easy but I can't for the life of me figure out the needed SQL query. I've found some related questions/answers but not quite exactly what I'm running into.

I'm trying to get a count of records for the past 7 days of the week and grouping it by branch location and also including 0 when no records are found. One thing that everyone said is that I needed to generate a calendar/date helper table and left join it. I've gone and done this and I now have a calendar table with the dates between 2000-01-01 and 2040-01-01.

Here is what my table structure looks like:

Records | location | date | thing | |----------|------------|---------| | Branch 1 | 2017-04-01 | Thing 1 | | Branch 2 | 2017-04-03 | Thing 2 | | Branch 1 | 2017-04-03 | Thing 3 | | Branch 1 | 2017-04-01 | Thing 4 | | Branch 3 | 2017-04-01 | Thing 5 | | Branch 3 | 2017-04-02 | Thing 6 | | Branch 1 | 2017-04-02 | Thing 7 | | Branch 2 | 2017-04-07 | Thing 8 |

Let's pretend that it's currently 2017-04-07. Notice that not all dates between 2017-04-01 and 2017-04-07 inclusive are in the records table which is why I needed a calendar helper table. That being said, I'm trying to get the following output:

Output | location | date | count(things)| |----------|------------|--------------| | Branch 1 | 2017-04-01 | 2 | | Branch 1 | 2017-04-02 | 1 | | Branch 1 | 2017-04-03 | 1 | | Branch 1 | 2017-04-04 | 0 | | Branch 1 | 2017-04-05 | 0 | | Branch 1 | 2017-04-06 | 0 | | Branch 1 | 2017-04-07 | 0 | | Branch 2 | 2017-04-01 | 0 | | Branch 2 | 2017-04-02 | 0 | | Branch 2 | 2017-04-03 | 1 | | Branch 2 | 2017-04-04 | 0 | | Branch 2 | 2017-04-05 | 0 | | Branch 2 | 2017-04-06 | 0 | | Branch 2 | 2017-04-07 | 1 | | Branch 3 | 2017-04-01 | 1 | | Branch 3 | 2017-04-02 | 1 | | Branch 3 | 2017-04-03 | 0 | | Branch 3 | 2017-04-04 | 0 | | Branch 3 | 2017-04-05 | 0 | | Branch 3 | 2017-04-06 | 0 | | Branch 3 | 2017-04-07 | 0 |

So, even if there is zero records, I still want to show a line for the location and the date (past 7 days). Is this even achievable?

Here is the query I've been messing around with:

SELECT
    `records`.`location`,
    `calendar`.`date`,
    COUNT(`records`.`thing`) AS `count`
FROM `records`
    RIGHT JOIN `calendar` ON `records`.`date` = `calendar`.`date`
WHERE `calendar`.`date` >= '2017-04-01' AND `calendar`.`date` <= '2017-04-07'
GROUP BY `calendar`.`date`, `records`.`location`
ORDER BY `records`.`location` ASC, `calendar`.`date` ASC

and

SELECT
    `records`.`location`,
    `date`.`ymd`,
    COUNT(`records`.`thing`) AS `count`
FROM (
    SELECT
        `calendar`.`date` AS `ymd`
    FROM `calendar`
    WHERE `calendar`.`date` >= '2017-04-01' AND `calendar`.`date` <= '2017-04-07'
) `date`
    LEFT JOIN `records` ON `date`.`ymd` = `records`.`date`
GROUP BY `records`.`location`, `date`.`ymd`

Both queries give me the same results which isn't even close to what I'm looking for.

Please help!

Upvotes: 1

Views: 494

Answers (1)

Shadow
Shadow

Reputation: 34243

It is not just the dates that you need a complete list of, but the branches as well. I added a derived table that contains all locations and cross joined it to your previous resultset. Also, the location field in the select list and the group by clause must come from this derived table.

SELECT
    t.`location`,
    `calendar`.`date`,
    COUNT(`records`.`thing`) AS `count`
FROM `records`
RIGHT JOIN (`calendar` 
JOIN (select distinct location from records) t) ON `records`.`date` = `calendar`.`date` and t.location=records.location
WHERE `calendar`.`date` >= '2017-04-01' AND `calendar`.`date` <= '2017-04-07'
GROUP BY `calendar`.`date`, t.`location`
ORDER BY `records`.`location` ASC, `calendar`.`date` ASC

Upvotes: 1

Related Questions