CODEWITHSUNDEEP
mysql
EnexoOnoma
EnexoOnoma

Reputation: 8836

How to include missing days into my query?

below is the mySQL query I currently use

SELECT Date( timestamp ) AS day, Count(id) AS total 
FROM goals_data 
WHERE company = 1
AND Date( timestamp ) BETWEEN Subdate( Curdate( ) , 10 ) AND Curdate( ) 
GROUP BY day desc

in order to get the output from the mySQL as:

2018-04-09 ------ 3
2018-04-08 ------ 6
2018-04-07 ------ 2
2018-04-05 ------ 4

The problem is that, as you can see from the example output above, there is no 2018-04-06 because there were no any entries that day.

What I want to achieve is to have a consequence of days, even if there are no entries found.

So the optimum is:

2018-04-09 ------ 3
2018-04-08 ------ 6
2018-04-07 ------ 2
2018-04-06 ------ 0
2018-04-05 ------ 4

How can I do this by editing my query?

Upvotes: 1

Views: 87

Answers (2)

Will B.
Will B.

Reputation: 18416

As mentioned in my comments and as suggested by others, you would need to generate the date range in MySQL.

Using the answer I marked as a duplicate to generate the date range, you would then add your query as a LEFT JOIN to retrieve the result set totals, matching on the generated date to the goals_data timestamp, moving the company criteria to a subquery.

SELECT a.Date as day, COUNT(gd.id) AS total
FROM (
    SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    FROM (SELECT 0 as a union all SELECT 1 union all SELECT 2 union all SELECT 3 union all SELECT 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9) as a
    CROSS JOIN (SELECT 0 as a union all SELECT 1 union all SELECT 2 union all SELECT 3 union all SELECT 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9) as b
    CROSS JOIN (SELECT 0 as a union all SELECT 1 union all SELECT 2 union all SELECT 3 union all SELECT 4 union all SELECT 5 union all SELECT 6 union all SELECT 7 union all SELECT 8 union all SELECT 9) as c
) a
LEFT JOIN (SELECT * FROM goals_data WHERE company = 1) AS gd
ON DATE(gd.`timestamp`) = DATE(a.Date)
WHERE DATE(a.Date) BETWEEN SUBDATE(CURDATE(), INTERVAL 10 DAY) AND CURDATE()
GROUP BY day DESC;

Which produces: http://www.sqlfiddle.com/#!9/17f03f/2

|        day | total |
|------------|-------|
| 2018-04-11 |     0 |
| 2018-04-10 |     0 |
| 2018-04-09 |     3 |
| 2018-04-08 |     6 |
| 2018-04-07 |     2 |
| 2018-04-06 |     0 |
| 2018-04-05 |     4 |
| 2018-04-04 |     0 |
| 2018-04-03 |     0 |
| 2018-04-02 |     0 |
| 2018-04-01 |     0 |

Alternatively as I suggested in the comments, you can use PHP to generate the desired date range for the report to query against.

$interval = new \DateInterval('P10D');
$currentDate = new \DateTime;
$previousDate = clone $currentDate;
$previousDate->sub($interval);
/*
$query = 'SELECT Date( timestamp ) AS day, Count(id) AS `count`
FROM goals_data 
WHERE company = 1
AND Date( timestamp ) BETWEEN :previous_date AND :current_date
GROUP BY day desc'
*/
$dbDates = array_column($dbRows, null, 'day');
$period = new \DatePeriod($currentDate, DateInterval::createFromDateString('-1 day'), $interval->d);
foreach ($period as $date) {
    $key = $date->format('Y-m-d');
    if (!array_key_exists($key, $dbDates)) {
        $dbDates[$key] = ['day' => $key, 'count' => 0];
    }
    echo $dbDates[$key]['day'] . ' ----- ' . $dbDates[$key]['count'] . \PHP_EOL;
}

Producing the same results: https://3v4l.org/YrpU8

2018-04-11 ----- 0
2018-04-10 ----- 0
2018-04-09 ----- 3
2018-04-08 ----- 6
2018-04-07 ----- 2
2018-04-06 ----- 0
2018-04-05 ----- 4
2018-04-04 ----- 0
2018-04-03 ----- 0
2018-04-02 ----- 0
2018-04-01 ----- 0

Upvotes: 1

Chris
Chris

Reputation: 17

I've previously achieved this by using a separate table/query already containing the date range. You can then left join along the date table so all of the dates were included along with matching values from the right table.

Upvotes: 0

Related Questions