CODEWITHSUNDEEP
sqlsql-serverdatabase
Omar Fayez
Omar Fayez

Reputation: 103

SQL 'Average' dilemma

I'm fairly new to SQL; For my university project we're creating a database for games, and reviewing said games. My issue is that I have a Table titled "Games", where it includes multiple attributes including a derived attribute 'Rating'; another table exists which is Member_rates_games, which includes 4 values, Uniqueness, Graphics, Design, and Interactivity. Now the issue is I don't know how to compute the 'Rating' Attribute using these 4 values in the other table. I Tried rating AS AVG(Uniquness, Interactivity, Design, Graphics), but it didn't work, the error being that it needs an argument. So how do I solve this? Do I resort to Joins? or is there another work around?

Here's the Schema if it can help:

Games (game_ID, name, release_date, age_limit, rating, email*)

Games_Rates_Members (game_ID, email, design, interactivity, graphics, uniqueness)

Any help would be greatly appreciated. I'm using Visual Studio w/SQL Server, Not MYSQL. Thanks in advance.

EDIT: Looks like I worded the problem poorly, let my try again, So this is my current SQL Code:

    CREATE TABLE Games(
    game_id int IDENTITY,
    name varchar (30) NOT NULL, 
    release_date date, 
    age_limit int NOT NULL, 
    rating int, 
    email varchar (50) NOT NULL, 
    CONSTRAINT PK_Games PRIMARY KEY (game_id), 
    CONSTRAINT FK_Games FOREIGN KEY (email) References Development_teams )

    CREATE TABLE Games_Rates_Members(
    game_id int NOT NULL, 
    email varchar (50) NOT NULL,
    design int NOT NULL,
    interactivity int NOT NULL,
    graphics int NOT NULL,
    uniqueness int NOT NULL,
    CONSTRAINT PK_Games_Rates_Members PRIMARY KEY (game_id, email),
    CONSTRAINT FK_Games_Rates_Members_1 FOREIGN KEY (game_id) References Games,
    CONSTRAINT FK_Games_Rates_Members_2 FOREIGN KEY (email) References Members
    )

Now, my issue is when I insert a value to the games table, in the rating attribute, what do I do in order for this rating attribute to be equivalent to the average of the 4 Values in the Games_Rates_Members Table. I hope this is more clear, excuse my English not being clear enough. Thanks again.

Upvotes: 0

Views: 96

Answers (2)

Hogan
Hogan

Reputation: 70538

I think it is poor design to expect a value to populated automatically in the games table. This would mean every change to the ratings table would cascade a change to the games table.

This is not a leading or best practice with relational databases. In a relation database you define the relations of the data, not the data generation rules. Leave that to your select statements. You want to get an average for every game calculate it when you select the game. You could do that like this:

SELECT Games.game_id, AVG((GRM.design,GRM.interactivity,GRM.graphics,GRM.uniqueness)/4) as rating
FROM Games
JOIN Games_Rates_Members GRM ON Games.game_id = GRM.game_id
GROUP BY Games.game_id

Sidenote, I do not recommend using email as a user key. What happens if someone changes their email? Instead use an assigned unique integer key as the key.

Upvotes: 1

elvainch
elvainch

Reputation: 1397

Assuming those 4 values are numeric and you have many reviews for the same game from different users. 

select game_ID, avg((design +  interactivity + graphics + uniqueness)/4) as rating
from Games_Rates_Members 
group by game_ID

Upvotes: 0

Related Questions