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

Question

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

score 2 · Accepted Answer · edited Jul 10 '19 at 06:18

2

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;

edited Jul 10 '19 at 06:18

Dan Leveille

3,001
2
24
28

answered Jun 28 '19 at 02:56

Gordon Linoff

1,242,037
58
646
786

Thanks so much! I was thinking about doing something like this, but it felt like bad practice though, right? Especially if I were to expand it to 100 or so years? – Dan Leveille Jun 28 '19 at 03:00
1

No, it is quite OK and fast. Can be used for days as well: https://stackoverflow.com/questions/2157282/generate-days-from-date-range/45951982. A bit hard for readers :) – fifonik Jun 28 '19 at 03:02

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

1 Answers1