title: SQL query to get a sum for each day between two dates
body: <p>I want to view the sum of rewards for a specific name between two dates</p>
  
  <p>This is MyTable</p>
  
  <pre><code>|   NAME    |  REWARD   |    DATE      |
  +-----------+-----------+--------------+
  |   Chris   |    yes    |  05.05.2018  |
  |   Chris   |    yes    |  05.05.2018  |
  |   Chris   |    no     |  07.05.2018  |
  |   John    |    yes    |  10.05.2018  |
  </code></pre>
  
  <p>Let's say the name I am looking for is "Chris" and the date is between 04.05.2018 - 08.05.2018. The query should also count the REWARD="yes" fields for each day and add the amount value "0" for days when no reward was earned.</p>
  
  <p>Then this should be the result:</p>
  
  <pre><code>|   NAME    |  AMOUNT   |    DATE      |
  +-----------+-----------+--------------+
  |   Chris   |    0      |  04.05.2018  |
  |   Chris   |    2      |  05.05.2018  |
  |   Chris   |    0      |  06.05.2018  |
  |   Chris   |    0      |  07.05.2018  |
  |   Chris   |    0      |  08.05.2018  |
  </code></pre>
  
  <p>I am using Firebird 2.5</p>
  
  <p>I tried this query, but the missing dates with "0" amount are not generated when doing it this way</p>
  
  <pre><code>SELECT name, SUM(CASE WHEN reward='yes' THEN 1 ELSE 0 END) AS AMOUNT, DATE
    from MyTable 
    WHERE DATE between '04.05.2018' and '08.05.2018'  
      AND NAME='Chris' 
    GROUP BY NAME, DATE
  </code></pre>