title: How to get missing values in date range?
body: <p>I have table with following structure:</p>
  
  <p><img src="https://i.stack.imgur.com/2LUHU.png" alt="enter image description here"></p>
  
  <p>And I'm trying to get grouped values between two dates, problem is, that i would like to have also retuned rows for dates which are not in select, for example i have range for </p>
  
  <pre><code>WHERE m.date BETWEEN "2014-09-02" AND "2014-09-10"
  </code></pre>
  
  <p>But for example in date 2014-09-06 is no related row in table, so in result should be</p>
  
  <pre><code> 2014-09-06| 0 | 0 | 0 | 0
  </code></pre>
  
  <p>How can i do it please? (if is it possible with SQLLite database).</p>
  
  <p><strong>Here is the query which i'm using:</strong> </p>
  
  <pre><code>  SELECT substr(m.date, 1, 10) as my_date, COUNT(m.ID) AS 'NUMBER_OF_ALL_CALLS',
      (SELECT COUNT(*) FROM dialed_calls subq WHERE subq.call_result = 'DONE'
      AND substr(m.date, 1, 10) = substr(subq.DATE, 1, 10)) as 'RESULT_DONE',
      (SELECT COUNT(*) FROM dialed_calls subq WHERE subq.call_result = 'NOT_INTERESTED' 
      AND substr(m.date, 1, 10) = substr(subq.DATE, 1, 10)) as 'RESULT_NOT_INTERESTED',
      (SELECT COUNT(*) FROM dialed_calls subq WHERE subq.call_result = 'NO_APPOINTMENT'
      AND substr(m.date, 1, 10) = substr(subq.DATE, 1, 10)) as 'RESULT_NO_APP'
      FROM dialed_calls m
      WHERE m.date BETWEEN "2014-09-02" AND "2014-09-05"
      GROUP BY my_date
  </code></pre>
  
  <p>Many thanks for any help. </p>
  
  <p><strong>Table structure:</strong></p>
  
  <pre><code>BEGIN TRANSACTION;
  
  CREATE TABLE dialed_calls(Id integer PRIMARY KEY,
  'date' datetime,
  'called_number' VARCHAR(45),
  'call_result' VARCHAR(45),
  'call_duration' INT,
  'synced' BOOL);
  
  /* Create few records in this table */
  INSERT INTO dialed_calls VALUES(1,'2014-09-02 15:54:34+0200',
  '800123456', 'NOT_INTERESTED', 10, 0);
  INSERT INTO dialed_calls VALUES(2,'2014-09-02 15:56:30+0200',
  '800123456', 'NO_APPOINTMENT', 10, 0);
  INSERT INTO dialed_calls VALUES(3,'2014-09-02 16:01:49+0200',
  '800123456', 'DONE', 9, 0);
  INSERT INTO dialed_calls VALUES(4,'2014-09-02 16:03:03+0200',
  '800123456', 'NO_APPOINTMENT', 69, 0);
  INSERT INTO dialed_calls VALUES(5,'2014-09-02 18:09:34+0200',
  '800123456', 'NO_APPOINTMENT', 3, 0);
  INSERT INTO dialed_calls VALUES(6,'2014-09-02 18:54:02+0200',
  '123456789', 'NO_APPOINTMENT', 89, 0);
  INSERT INTO dialed_calls VALUES(7,'2014-09-02 18:55:25+0200',
  '123456789', 'NOT_INTERESTED', 89, 0);
  INSERT INTO dialed_calls VALUES(8,'2014-09-03 18:36:58+0200',
  '123456789', 'DONE', 185, 0);
  INSERT INTO dialed_calls VALUES(9,'2014-09-04 18:36:58+0200',
  '123456789', 'DONE', 185, 0);
  INSERT INTO dialed_calls VALUES(10,'2014-09-05 18:36:58+0200',
  '123456789', 'DONE', 185, 0);
  COMMIT;
  </code></pre>