-2

I'm using SQL Server with a table about video game sales from the years (1977-2020). The query below is what I've been using to solve the problem: finding the #1 game with the highest sales for each year. When executed the output gives me multiple of the same years and video games that are 10yrs ahead apart from where they should be.

SELECT VG1.Name, VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) AS Total_Sales
FROM Video_Game_Sales VG1
INNER JOIN Video_Game_Sales VG2 ON VG1.Name = VG2.Name
INNER JOIN  Video_Game_Sales VG3 ON VG2.Global_sales = VG3.Global_sales
INNER JOIN Video_Game_Sales VG4 ON VG3.Release_Date = VG4.Release_Date
GROUP BY VG4.Release_Date, VG1.Name
ORDER BY VG4.Release_Date, ROUND(SUM(VG3.Global_sales),2) DESC;

I want only one game for each year between 1977-2020 with the highest total sold.

Desired results:

Release_year Title Total_Sales
1977 Combat 1.25
1978 Space Invaders 2.53
1980 Asteroids 4.31
Dale K
  • 25,246
  • 15
  • 42
  • 71
Nickolas Jaramillo
  • 3
  • 1
  • 2
    You shouldn't delete a question and then re-ask it - you should edit your original question. – Dale K Feb 24 '23 at 03:06
  • 1
    And as asked on your original question, please provide sample data that would produce these desired results. Keep in mind the *minimal* from [mre] i.e. if you can simplify your query and still demonstrate the issue then please do. – Dale K Feb 24 '23 at 03:07
  • you are grouping by release date. that is a day. you want years. group by year. – Hogan Feb 24 '23 at 04:50
  • This is a faq. Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. – philipxy Feb 25 '23 at 12:20

1 Answers1

1

Assuming there is no duplicate records for each game (otherwise, you'll need to handle this first), the first step is to get the top selling games for each year (we'll filter the desired years on this subquery). Then we join this result back with the original table, in order to get the Name of the game.

Query:

SELECT
  top_sellers.Release_Year,
  sales.Name AS Title,
  top_sellers.Global_sales AS Total_Sales
FROM (
  SELECT YEAR(Release_Date) AS Release_Year, MAX(Global_sales) AS Global_sales
  FROM Video_Game_Sales
  WHERE YEAR(Release_Date) BETWEEN '1977' AND '2020'
  GROUP BY YEAR(Release_Date)
) AS top_sellers
JOIN Video_Game_Sales AS sales
ON top_sellers.Global_sales = sales.Global_sales
AND top_sellers.Release_Year = YEAR(sales.Release_Date)
ORDER by Release_Year ASC

Input:

Name Release_Date Global_sales
Red Dead Redemption 2 2018-10-26 50000000
Super Smash Bros. Ultimate 2018-12-7 30440000
Call of Duty: Modern Warfare 2019-10-25 30000000
Pokémon Sword / Shield 2019-11-15 25680000
Animal Crossing: New Horizons 2020-03-20 41590000

Output:

Release_Year Title Total_Sales
2018 Red Dead Redemption 2 50000000
2019 Call of Duty: Modern Warfare 30000000
2020 Animal Crossing: New Horizons 41590000

Demo in sqlfiddle.

Lawrence Fernandes
  • 146
  • 5