Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm trying to create SELECT statement with a GROUP BY clause, which should return "default values".

Imagine the following simple MySQL table:

CREATE TABLE `tracker` (
  `id` INTEGER PRIMARY KEY auto_increment,
  `date` DATETIME NOT NULL,
  `customer_id` INTEGER NOT NULL
The table contains only one record:
INSERT INTO `tracker` (`date`, `customer_id`) VALUES('2010-05-03', 1);
After wards I'm executing the following SQL query:
SELECT DATE(`date`), COUNT(customer_id) FROM tracker
WHERE DATE(`date`) >= '2010-05-01' AND DATE(`date`) <= '2010-05-05'
GROUP BY DATE(`date`) ORDER BY DATE(`date`);
And get the expected result set:
+----+---------------------+-------------+
| id | date                | customer_id |
+----+---------------------+-------------+
|  1 | 2010-05-10 00:00:00 |           1 |
+----+---------------------+-------------+
However, I would like the result set to look like this:
+--------------+--------------------+
| DATE(`date`) | COUNT(customer_id) |
+--------------+--------------------+
| 2010-05-01   |                  0 |
| 2010-05-02   |                  0 |
| 2010-05-03   |                  1 |
| 2010-05-04   |                  0 |
| 2010-05-05   |                  0 |
+--------------+--------------------+
Is it possible to achieve this behavior?
                There are quite a few duplicates with this same theme, for example stackoverflow.com/questions/400759/…
– Unreason
                May 10, 2010 at 10:26
You could build a temporary table of the valid dates in the range and then incorporate that into your query - that's about the only way forward that I can immediately see...
Martin
As Martin said, the best solution is to create a temp table with dates.
Then there's 2 approaches:
Do an outer join with that temp table and do a group by on result, OR
group by on the original table + UNION select date,0 as count from date_table d where not exists (select 1 from customer c where c.date=d.date)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.