How to find the number of video games released triannually?

Question

Lets say i have a table videogames and I want to find the number of games released in in intervals of 3 years starting from year 1997.

videogames

videogameid	title	year
1	GoldenEye 007	1997
2	Tomb Raider II	1997
3	Half-Life	1998
4	The Sims	2000
5	GTA (III)	2001
6	Kingdom Hearts	2003
7	World Of Warcraft	2004
8	ES4: Oblivion	2006
9	L.A. Noire	2011
10	Far Cry 3	2012
11	Diablo III	2012

From the table, the expected output should be Year (1997-1999) = 3, Year (2000-2002) = 2, Year(2003 - 2005) = 2, Year(2006-2008) = 1, Year (2009 - 2011 ) = 1 and Year (2012-2014)= 2

This is my attempt at solving the code:

SELECT COUNT(videogameid) AS number_of_videogames
FROM videogames 
WHERE INTERVAL(1997,2,2,2,2,2,2)
GROUP BY YEAR;

For some reason, I got returned back more than 100 ++ rows of answers when they should only be 6 rows for each interval.

Answer 1

About the simplest I think you can get is to use a recursive CTE to generate your years and simply outer-join this to your source data and group into 3 like so:

with recursive n as (
    select Min(year) yn, Ceiling((max(year)-Min(year))/3.0)*3 + min(year) maxyear, Min(year) minyear 
    from t
    union all
    select yn + 1, maxyear, minyear
    from n
    where yn < maxyear
)
select Min(yn) FromYear, Max(yn) toYear, Count(year) qty
from n
left join t on t.year=yn
group by floor((yn - minyear) /3);

Demo DB<>Fiddle