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

Update 2022-03-06

It seems strange to list the totals with no reference year numbers :-) but if that's all you want, try:

WITH recursive ReleaseYears AS (
  -- build list of years 
  SELECT MIN(`year`) AS YearNum
  FROM   VideoGames
  UNION ALL
  SELECT YearNum + 1
  FROM   ReleaseYears
  WHERE  YearNum <= ( SELECT MAX(`year`) + 2 FROM VideoGames) 
)
SELECT COUNT(vg.videogameid) AS Num
FROM   ( -- group year numbers in groups of 3 
         SELECT FLOOR((ROW_NUMBER() OVER(ORDER BY (SELECT YearNum)) -1) / 3) + 1 AS YearGroup
               , YearNum
         FROM   ReleaseYears
       ) y LEFT JOIN VideoGames vg ON y.YearNum = vg.`year`
GROUP BY YearGroup       
HAVING COUNT(vg.videogameid) > 0

Results:

| Num |
| --: |
|   3 |
|   2 |
|   2 |
|   1 |
|   1 |

---

Original Answer

If you want to display the year in format "(min-max)":

WITH recursive ReleaseYears AS (
  SELECT MIN(`year`) AS YearNum
  FROM   VideoGames
  UNION ALL
  SELECT YearNum + 1
  FROM   ReleaseYears
  WHERE  YearNum <= ( SELECT MAX(`year`) + 2 FROM VideoGames) 
)
SELECT YearGroup
       , CONCAT_WS('-', MIN(YearNum), MAX(YearNum)) AS YearRange
       , COUNT(vg.videogameid) AS Num
FROM   (
         SELECT FLOOR((ROW_NUMBER() OVER(ORDER BY (SELECT YearNum)) -1) / 3) + 1 AS YearGroup
               , YearNum
         FROM   ReleaseYears
       ) y LEFT JOIN VideoGames vg ON y.YearNum = vg.`year`
GROUP BY YearGroup       
HAVING COUNT(vg.videogameid) > 0
;

Results:

YearGroup | YearRange | Num
--------: | :-------- | --:
        1 | 1997-1999 |   3
        2 | 2000-2002 |   2
        3 | 2003-2005 |   2
        4 | 2006-2008 |   1
        5 | 2009-2011 |   1
        6 | 2012-2014 |   2

db<>fiddle here