I want to find how many movies have been made in every decade-CodePudding

Tried this but it shows DECADE column and every row is NULL.

SELECT ReleaseDate, COUNT (Title),
CASE
        WHEN ReleaseDate BETWEEN 2010 AND 2019 THEN '2010 - 2019'
        WHEN ReleaseDate BETWEEN 2000 AND 2009 THEN '2000 - 2009'
        WHEN ReleaseDate BETWEEN 1990 AND 1999 THEN '1990 - 1999'
        WHEN ReleaseDate BETWEEN 1980 AND 1989 THEN '1980 - 1989'
        WHEN ReleaseDate BETWEEN 1970 AND 1979 THEN '1970 - 1979'
        WHEN ReleaseDate BETWEEN 1960 AND 1969 THEN '1960 - 1969'
        END AS DECADE
FROM Film
GROUP BY ReleaseDate

CodePudding user response：

You can get the decade with an integer division. So, not CASE statement is required, but you must GROUP BY decades

SELECT
    CONVERT(nvarchar(3), YEAR(ReleaseDate) / 10)   '0 - '  
      CONVERT(nvarchar(3), YEAR(ReleaseDate) / 10)   '9' AS Decade,
    Count(*) AS [Count]
FROM Film
GROUP BY YEAR(ReleaseDate) / 10
ORDER BY YEAR(ReleaseDate) / 10

I assume that ReleaseDate has a date or datetime type.

The result will be something like this:

Decade	Count
1990 - 1999	14
2000 - 2009	17
2010 - 2019	23
2020 - 2029	3

Example: If the date is 2012-08-17, then YEAR(ReleaseDate) is 2012, 2012 / 10 is 201. We convert it to a nvarchar(3) -> '201' and add the string '0 - ' which gives '2010 - '. Then we do the same again but add '9' instead to get 2019. Result: '2010 - 2019'.

The advantage of this approach is that you get automatically all the decades stored in your db. When a new decade starts, you get it automatically without have to complement your CASE expression.

CodePudding user response：

Actually you may want to use a CROSS APPLY so that you can reference the alias

FYI - Technically the decade begins 2001

Example

SELECT B.Decade
      ,COUNT(*),
FROM Film A
CROSS APPLY ( values (
                    CASE  WHEN year(ReleaseDate) BETWEEN 2010 AND 2019 THEN '2010 - 2019'
                          WHEN year(ReleaseDate) BETWEEN 2000 AND 2009 THEN '2000 - 2009'
                          WHEN Year(ReleaseDate) BETWEEN 1990 AND 1999 THEN '1990 - 1999'
                          WHEN year(ReleaseDate) BETWEEN 1980 AND 1989 THEN '1980 - 1989'
                          WHEN year(ReleaseDate) BETWEEN 1970 AND 1979 THEN '1970 - 1979'
                          WHEN year(ReleaseDate) BETWEEN 1960 AND 1969 THEN '1960 - 1969'
                     END 
                     ) 
            ) B(Decade)
GROUP BY B.Decade

CodePudding user response：

I am guessing the issue might be in the format of timestamp, try using YEAR() on ReleaseDate like so

CASE 
    WHEN YEAR(ReleaseDate) BETWEEN 2010 AND 2019 THEN '2010 - 2019' 
    ...
    ...
END

CodePudding user response：

ReleaseDate seems to be a date, in which case you can use date ranges:

SELECT ReleaseDate, COUNT (Title),
CASE
        WHEN ReleaseDate BETWEEN '2010-01-01 00:00:00' AND '2019-01-01 00:00:00' THEN '2010 - 2019'
        WHEN ReleaseDate BETWEEN '2000-01-01 00:00:00' AND '2009-01-01 00:00:00' THEN '2000 - 2009'
        WHEN ReleaseDate BETWEEN '1990-01-01 00:00:00' AND '1999-01-01 00:00:00' THEN '1990 - 1999'
        WHEN ReleaseDate BETWEEN '1980-01-01 00:00:00' AND '1989-01-01 00:00:00' THEN '1980 - 1989'
        WHEN ReleaseDate BETWEEN '1970-01-01 00:00:00' AND '1979-01-01 00:00:00' THEN '1970 - 1979'
        WHEN ReleaseDate BETWEEN '1960-01-01 00:00:00' AND '1969-01-01 00:00:00' THEN '1960 - 1969'
        END AS DECADE
FROM Film
GROUP BY ReleaseDate