I've tried the code below but it throws an error:

SELECT Publisher, SUM(Global_Sales) AS total_games_sold, Year,
CASE 
WHEN Year < 1989 THEN SUM(Global_Sales)
WHEN Year BETWEEN 1990 AND 1999 THEN SUM(Global_Sales)
WHEN Year BETWEEN 2000 AND 2009 THEN SUM(Global_Sales)
ELSE Year > 2009 END AS Dates
FROM Vgsales
ORDER BY Year;

CodePudding user response：

If I understand your issue correctly, you need to use SUM with conditions:

SELECT 
   Publisher, 
   SUM(Global_Sales) AS [total_games_sold], 
   SUM(CASE WHEN Year < 1989 THEN Global_Sales END) AS [1989_games_sold],
   SUM(CASE WHEN Year BETWEEN 1990 AND 1999 THEN Global_Sales END) AS [1990_1999_games_sold],
   SUM(CASE WHEN Year BETWEEN 2000 AND 2009 THEN Global_Sales END) AS [2000_2009_games_sold],
   SUM(CASE WHEN Year > 2009 THEN Global_Sales END) AS [2009_games_sold]
FROM Vgsales
GROUP BY Publisher
ORDER BY Publisher

CodePudding user response：

You can try doing UNION.

SELECT Publisher, SUM(Global_Sales) AS total_games_sold, '<1989' AS Year
FROM Vgsales
WHERE YEAR < 1989 
GROUP BY Publisher

UNION ALL

SELECT Publisher, SUM(Global_Sales) AS total_games_sold, '1990-1999' AS Year
FROM Vgsales
WHERE YEAR BETWEEN 1990 AND 1999
GROUP BY Publisher

UNION ALL

SELECT Publisher, SUM(Global_Sales) AS total_games_sold, '2000-2009' AS Year
FROM Vgsales
WHERE YEAR BETWEEN 2000 AND 2009
GROUP BY Publisher

UNION ALL

SELECT Publisher, SUM(Global_Sales) AS total_games_sold, '>2009' AS Year
FROM Vgsales
WHERE Year > 2009
GROUP BY Publisher;

This should give you something like