SQL Help
Maybe this should go on stack overflow, but I thought some of you may be interested in my mini-project.
I made a tool to collect user scores and critic scores of video games on metacritic, loading this to an SQL CE database. My intention is to correlate critics who are statistically 'bad' - dissimilar to user scores. I started this after ME3 appeared, getting 10/10 from critics then 4/10 from users.
Tables:
C_GAMES (NAME nvarchar(100), PLATFORM nvarchar(10), URL nvarchar(255), SCORE decimal(3,1), QUERY_TIME datetime, RELEASE_YEAR smallint, ID int NOT NULL PRIMARY KEY IDENTITY(1,1))
C_CRITICS (NAME nvarchar(100), ID int NOT NULL PRIMARY KEY IDENTITY(1,1))
C_REVIEWS (GAME_ID int NOT NULL, CRITIC_ID int NOT NULL, SCORE decimal, QUERY_TIME datetime, ID int NOT NULL PRIMARY KEY IDENTITY(1,1))
For 2011 - I have 778 games, 1781 critics and 22350 reviews.
Ideally I want to create a query that isolates critics who have enough reviews to return accurate averages. Only 121 of these critics have more than 50 reviews, with the majority having only 1.
SELECT CRITIC_ID, COUNT(GAME_ID) AS TOTAL
FROM C_REVIEWS
GROUP BY CRITIC_ID
Is there a way to get this to filter out results that are below 50? I get SQL errors when I try to add WHERE TOTAL > 50.
Also any suggestions on a more fair way to calculate this? Does averaging seem fair? Final query would be like this:
SELECT CRITIC_ID, AVG(ABS(C_REVIEWS.SCORE - (C_GAMES.SCORE * 10))) as SCORE_DIFFERENCE, COUNT(GAME_ID) as TOTAL
FROM C_REVIEWS JOIN C_GAMES ON C_REVIEWS.GAME_ID = C_GAMES.ID
GROUP BY C_REVIEWS.CRITIC_ID ORDER BY TOTAL DESC
Thanks for any help.
I made a tool to collect user scores and critic scores of video games on metacritic, loading this to an SQL CE database. My intention is to correlate critics who are statistically 'bad' - dissimilar to user scores. I started this after ME3 appeared, getting 10/10 from critics then 4/10 from users.
Tables:
C_GAMES (NAME nvarchar(100), PLATFORM nvarchar(10), URL nvarchar(255), SCORE decimal(3,1), QUERY_TIME datetime, RELEASE_YEAR smallint, ID int NOT NULL PRIMARY KEY IDENTITY(1,1))
C_CRITICS (NAME nvarchar(100), ID int NOT NULL PRIMARY KEY IDENTITY(1,1))
C_REVIEWS (GAME_ID int NOT NULL, CRITIC_ID int NOT NULL, SCORE decimal, QUERY_TIME datetime, ID int NOT NULL PRIMARY KEY IDENTITY(1,1))
For 2011 - I have 778 games, 1781 critics and 22350 reviews.
Ideally I want to create a query that isolates critics who have enough reviews to return accurate averages. Only 121 of these critics have more than 50 reviews, with the majority having only 1.
SELECT CRITIC_ID, COUNT(GAME_ID) AS TOTAL
FROM C_REVIEWS
GROUP BY CRITIC_ID
Is there a way to get this to filter out results that are below 50? I get SQL errors when I try to add WHERE TOTAL > 50.
Also any suggestions on a more fair way to calculate this? Does averaging seem fair? Final query would be like this:
SELECT CRITIC_ID, AVG(ABS(C_REVIEWS.SCORE - (C_GAMES.SCORE * 10))) as SCORE_DIFFERENCE, COUNT(GAME_ID) as TOTAL
FROM C_REVIEWS JOIN C_GAMES ON C_REVIEWS.GAME_ID = C_GAMES.ID
GROUP BY C_REVIEWS.CRITIC_ID ORDER BY TOTAL DESC
Thanks for any help.
Comments
NAME, REVIEW_TOTAL, SCORE_DIFFERENCE
Playstation Official Magazine UK, 220, 19.17
GameShark, 193, 19.06
Game Chronicles, 226, 16.99
TotalPlayStation, 104, 16.92
Gaming Age, 201, 16.89
Official Xbox Magazine, 226, 16.83
Gaming Nexus, 186, 16.62
PSM3 Magazine UK, 130, 16.09
TheSixthAxis, 119, 16.03
X360 Magazine UK, 191, 15.92
and the best 10...
Eurogamer Spain, 121, 10.59
Extreme Gamer, 141, 11.15
Level7.nu, 124, 11.16
Gamer.nl, 175, 11.34
Eurogamer Portugal, 127, 11.42
GameTrailers, 176, 11.67
Everyeye.it, 184, 12.07
VideoGamer, 198, 12.21
Giant Bomb, 127, 12.28
PALGN, 156, 12.36
Worse 5 for small publications...
PC Games (Germany), 39, 19.08
Push Square, 31, 18.16
GameCritics, 82, 17.59
Gamers.at, 99, 17.49
Official PlayStation Magazine Benelux, 59, 17.17
And the best 5...
GameZone, 49, 8.63
D+PAD Magazine, 83, 10.84
The Escapist, 76, 11.14
Guardian, 73, 11.34
Games.cz, 85, 11.59
declaring great success
group by critic_id
having count(game_id) < 50
SELECT q1.CRITIC_ID FROM
(SELECT CRITIC_ID, COUNT(GAME_ID) AS TOTAL
FROM C_REVIEWS
GROUP BY CRITIC_ID) as q1 WHERE q1.TOTAL > 50;
group by critic_id
having count(game_id) < 50<!--QuoteEnd--></div><!--QuoteEEnd-->
Ah thanks you are correct.