我希望能够统计两个日期之间(包括上周的今天)所有条目的数量,并且如果没有条目,则选择0。当前输出结果如下:
然而,我需要像这样打印的内容:
我的查询看起来像这样:
我做错了什么?
+-------+------------+
| items | SellDate |
+-------+------------+
| 1 | 2017-01-01 |
+-------+------------+
| 3 | 2017-01-02 |
+-------+------------+
| 1 | 2017-01-03 |
+-------+------------+
| 5 | 2017-01-06 |
+-------+------------+
然而,我需要像这样打印的内容:
+-------+------------+
| items | SellDate |
+-------+------------+
| 1 | 2017-01-01 |
+-------+------------+
| 3 | 2017-01-02 |
+-------+------------+
| 1 | 2017-01-03 |
+-------+------------+
| 0 | 2017-01-04 |
+-------+------------+
| 0 | 2017-01-05 |
+-------+------------+
| 5 | 2017-01-06 |
+-------+------------+
| 0 | 2017-01-07 |
+-------+------------+
我的查询看起来像这样:
SELECT
COUNT(Item.id) AS Items,
DATE(Item.sold_at) AS SellDate
FROM Item
WHERE Item.sold_at IS NOT NULL AND Item.sold_at BETWEEN DATE(DATETIME('now', 'localtime', '-6 days')) AND DATE(DATETIME('now', 'localtime', '+1 day'))
GROUP BY SellDate
我做错了什么?