Choose a counter between date range

Question

I would like to count all the records between two dates (last week until today), and if they are not there, select 0. Currently, it prints as follows:

+-------+------------+
| items | SellDate   |
+-------+------------+
| 1     | 2017-01-01 |
+-------+------------+
| 3     | 2017-01-02 |
+-------+------------+
| 1     | 2017-01-03 |
+-------+------------+
| 5     | 2017-01-06 |
+-------+------------+

However, I need to print something like this:

+-------+------------+
| 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 |
+-------+------------+

My query looks like this:

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

What am I doing wrong?

Answer 1

As far as I know, this is not possible without the recursive generic table expression supported in SQLite 3.8.3 and later. With the appropriate version, you can do this by attaching a date range to the list of items:

WITH RECURSIVE dates(date) AS (
  VALUES(DATE(DATETIME('now', 'localtime', '-6 days')))
  UNION ALL
  SELECT date(date, '+1 day')
  FROM dates
  WHERE date < DATE(DATETIME('now', 'localtime', '+1 day'))
)
SELECT 
    date,
    COUNT(Item.id) AS Items
FROM 
    dates
LEFT JOIN
    Item
ON
    dates.date = Item.SellDate
GROUP BY
    SellDate