Include null values in sqlite result

Question

Hello sqlite3 experts,

i have a table called positionlogs with the following structure:

id | positionId | scanTime
 1 |          1 | 2019-05-11 14:18:58.802 +00:00
 2 |          2 | 2019-05-11 14:24:05.518 +00:00
 3 |          1 | 2019-05-11 14:34:01.777 +00:00
 4 |          1 | 2019-05-11 14:34:02.549 +00:00
 5 |          1 | 2019-05-11 14:34:06.560 +00:00
 6 |          1 | 2019-05-11 14:34:07.484 +00:00

and i execute the following query:

SELECT ranges.start, 
       ranges.end,
       count(positionLogs.id) as totalScans
 FROM 
 (
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-55 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-50 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-50 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-45 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-45 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-40 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-40 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-35 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-35 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-30 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-30 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-25 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-25 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-20 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-20 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-15 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-15 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-10 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-10 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-5 minutes') as end UNION ALL
  SELECT strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-5 minutes') as start, strftime('%Y-%m-%d %H:%M', '2019-05-11 14:35','-0 minutes') as end) as ranges
  LEFT join positionlogs on (positionlogs.scanTime >= ranges.start AND positionlogs.scanTime <=ranges.end)
   where positionlogs.positionId = 1
   group by start;

What i am trying to achieve is to get a result similar to the below:

start            | end                | totalScans
2019-05-11 13:40 | 2019-05-11 13:45   | 0
2019-05-11 13:45 | 2019-05-11 13:50   | 0
2019-05-11 13:50 | 2019-05-11 13:55   | 0
2019-05-11 13:55 | 2019-05-11 14:00   | 0
2019-05-11 14:00 | 2019-05-11 14:05   | 0
2019-05-11 14:05 | 2019-05-11 14:10   | 0
2019-05-11 14:10 | 2019-05-11 14:15   | 0
2019-05-11 14:15 | 2019-05-11 14:20   | 1
2019-05-11 14:20 | 2019-05-11 14:25   | 1
2019-05-11 14:25 | 2019-05-11 14:30   | 4
2019-05-11 14:30 | 2019-05-11 14:35   | 0

but instead of that my query returns this:

start            | end                | totalScans
2019-05-11 14:15 | 2019-05-11 14:20   | 1
2019-05-11 14:20 | 2019-05-11 14:25   | 1
2019-05-11 14:25 | 2019-05-11 14:30   | 4

I tried several different ways without success. Any help/suggestions are really appreciated!

Please use words to explain how the result is a function of the input. Otherwise we have to try to guess from your one example. Please in code questions give a [mcve]--cut & paste & runnable code; example input with desired & actual output (including verbatim error messages); clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS "minimal" — philipxy, May 11 '19 at 18:26
This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. — philipxy, May 11 '19 at 18:26
Learn what LEFT JOIN ON returns: INNER JOIN ON rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. A WHERE or INNER JOIN ON that requires a right [sic] table column to be not NULL after an OUTER JOIN ON removes any rows extended by NULLs, ie leaves only INNER JOIN ON rows, ie "turns OUTER JOIN into INNER JOIN". You have that. — philipxy, May 11 '19 at 18:27
Your title says "Include null values in sqlite result" but your query neither preserves nor counts nulls in columns that get nulls from left join. Research count(column) vs count (*). — philipxy, May 11 '19 at 20:11

score 0 · Accepted Answer · answered May 11 '19 at 18:14

Well, you left join positionlogs but then have a WHERE clause that wants positionlogs.positionid to be 1. Rows from ranges where no matching row from postionlog was found cannot satisfy this and are therefore filtered out. Move the expression from the WHERE to the ON clause to fix this.

SELECT ranges.start, 
       ...
       ... ranges
       LEFT JOIN positionlogs
                 ON positionlogs.scanTime >= ranges.start
                    AND positionlogs.scanTime <=ranges.end
                    AND positionlogs.positionId = 1
       GROUP BY ranges.start;

Include null values in sqlite result

1 Answers1