4

I would like to count all the entries between 2 dates (the last week until today included) and if there are none, select 0. Currently it prints like this :

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

However, I need something that print 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 look 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 I am doing wrong?

Chax
  • 1,041
  • 2
  • 14
  • 36
  • You are doing nothing "wrong". SQL doesn't make up date to put in the result set. The dates with zeros don't exist in the source data. – Gordon Linoff Sep 11 '17 at 01:09
  • please look at this. https://stackoverflow.com/questions/2651249/wanted-to-get-all-dates-in-mysql-result – Sate Wedos Sep 11 '17 at 02:02

1 Answers1

2

As far I know this is not possible without a recursive common table expression supported in SQLite 3.8.3 and higher. With the corresponding version, you can do it by joining date range with the items list:

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
Oleh Rybalchenko
  • 6,998
  • 3
  • 22
  • 36
  • 1
    This solution worked very well and the performance were good. One thing I slightly ajusted was the `+1 day`. I completly removed it since it was giving me tomorrow. The line is `WHERE date < DATE(DATETIME('now', 'localtime'))`. I also added `ORDER BY dates.date` after the `GROUP BY` because otherwise the dates are not in order. Anything that has a count of zero was on top. The rest was perfect. Thanks, I learned that I could use `RECURSIVE` today, I shall read on that ASAP – Chax Sep 11 '17 at 11:37