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 |
+----+---------------------+-------------+
H开发者_C百科owever, 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?
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, ORgroup 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)
精彩评论