title: Adding Missing Dates To Each Location - MySQL 5.7
body: <p>So my SQL output is:</p>
  <pre><code>DUNF    2021-04-01  18
  DUNF    2021-04-02  17
  DUNF    2021-04-03  7
  DUNF    2021-04-04  10
  DUNF    2021-04-05  18
  DUNF    2021-04-06  20
  DUNF    2021-04-07  17
  DUNF    2021-04-08  14
  LEEDS   2021-04-01  4
  LEEDS   2021-04-02  4
  LEEDS   2021-04-03  5
  LEEDS   2021-04-05  9
  LEEDS   2021-04-06  3
  LEEDS   2021-04-07  1
  LEEDS   2021-04-08  3
  etc.
  </code></pre>
  <p>But I need to have an entry for each day, even if the final field is a volume (sometimes nothing happens in that location on that day).</p>
  <p>I've tried everything, but my SQL 5.7 knowledge is weak. I can generate a calendar table, but it doesn't fill the gaps in each site (you can see that Leeds is missing a day, but there are other sites that are missing dates too).</p>
  <p>The code I'm using so far is:</p>
  <pre><code>SELECT location, begin_date, count(*) as volume
    FROM abs_raw_data
    WHERE begin_date &gt;= (SELECT date_sub(max(begin_date), interval 7 day) from abs_raw_data)
    GROUP BY location, begin_date
    ORDER BY location asc, begin_date asc
  </code></pre>
  <p>How would I even go about doing this?</p>