title: MySQL Transform a date-range into single rows/count days per year?
body: <p>I am looking for a solution to count days in a daterange per year. My table looks like this:</p>
  
  <pre><code>+----+-----------+------------+------------+
  | id | source_id | start_date | end_date   |
  +----+-----------+------------+------------+
  |  1 |         1 | 2015-11-01 | 2017-01-31 |
  +----+-----------+------------+------------+
  </code></pre>
  
  <p>Now I want to count the days in between. Its easy with DATEDIFF() in complete, but how to do it per year?</p>
  
  <p>I tried a kind of temp. transformation into single rows to perform count and group actions:</p>
  
  <pre><code>+----+-----------+------------+------------+
  | id | source_id | start_date | end_date   |
  +----+-----------+------------+------------+
  |  1 |         1 | 2015-11-01 | 2015-12-31 |
  +----+-----------+------------+------------+
  |  1 |         1 | 2016-01-01 | 2016-12-31 |
  +----+-----------+------------+------------+
  |  1 |         1 | 2017-01-01 | 2017-01-31 |
  +----+-----------+------------+------------+
  </code></pre>
  
  <p>EDIT:
  The desired output should like that:</p>
  
  <pre><code>+-----------+------+------+
  | source_id | year | days |
  +-----------+------+------+
  |         1 | 2015 |   60 |
  +-----------+------+------+
  |         1 | 2016 |  365 |
  +-----------+------+------+
  |         1 | 2017 |   30 |
  +-----------+------+------+
  </code></pre>
  
  <p>So it become possible to summarize all days grouped by source_id and year.</p>
  
  <p>Is there an easy way to do it in MySQL?</p>