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

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.

CodePudding user response：

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

CodePudding user response：

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