Count total running events per year with events that range over multiple years

I have a table of events that span over several years.

title     start_year  end_year 
Event 1   1976        1983
Event 2   1977        2002
Event 3   2018        2019
Event 4   2003        2019

Desired result:

I want to output the total number of “running” events for each year over the past 50 years. (In 1976, only one event was running. In 1977, the same event was still running plus another event.)

year   total_events 
1976   1
1977   2
1976   2

Example query:

This query returns the total for one year. What’s the best way to get this to output results for the past 50 years?

SELECT COUNT(*) AS total_events
FROM `events`
WHERE start_year <= 1976
AND end_year >= 1976

Answer

Basically, you need a list of years. If you don’t have one handy, you can create a list in a derived table:

select y.year,
       (select count(*)
        from t
        where y.year between t.start_year and t.end_year
       ) as num_events
from (select 1976 as year union all
      select 1977 as year union all
      select 1978 as year union all
      . . .
     ) y;