AVG(User_Score) in T-SQL is returning wrong value

Question

I imported an Excel sheet into SMSS about video games, there are around 10,000 User_Scores, but when I run the code below it seems to only give me the max user_score per publisher... I was looking to get the average user_score for each publisher (gaming company). Help! (I did previously have a where User_score is not null, but I found out AVG ignores null so I did remove it)

SELECT 
    Publisher, 
    AVG(User_Score) AS AVGScore, 
    AVG(User_Count) AS AVGCount 
FROM 
    [Video Game Sales]..Video_Games_Sales_as_at_22_Dec_$
GROUP BY
    Publisher, User_Score
ORDER BY
    AVGScore DESC

Nothing that the intellisense thought would work. I tried AVG(CAST(USER_SCORE AS AVGScore), my next attempt was going to try and use a sum/count of user_score.

Answer 1

Your query groups by the unique combination of Publisher and User_Score, which isn't what you wanted. Remove the User_Score from the group by clause and you should be OK:

SELECT   Publisher, AVG(User_Score) as AVGScore, AVG(User_Count) as AVGCount 
FROM     [Video Game Sales]..Video_Games_Sales_as_at_22_Dec_$
GROUP BY Publisher
ORDER BY AVGScore desc