CODEWITHSUNDEEP
mysqldatabaseformula
Travis123
Travis123

Reputation: 25

How to make a table column an average of two other columns

Is there a way that I could make the column type an equation of two other columns? Here is my table

CREATE TABLE `tbl_leaderboard` (
    `leaderboardID` INTEGER NOT NULL AUTO_INCREMENT,
    `totalScore` INTEGER DEFAULT 0 NOT NULL,
    `gamesPlayed` INTEGER DEFAULT 0 NOT NULL,
    `averageScore` INTEGER DEFAULT 0 NOT NULL,
    `player_score` INTEGER DEFAULT 0 NOT NULL,
    `userID` INTEGER NOT NULL,
    `playerID` INTEGER NOT NULL,
    PRIMARY KEY (`leaderboardID`),
    FOREIGN KEY (`userID`) REFERENCES `tbl_user`(`userID`)  ON DELETE CASCADE,
    FOREIGN KEY (`playerID`) REFERENCES `tbl_player`(`playerID`) ON DELETE CASCADE
);

I would like averageScore to equal totalscore/gamesPlayed. Is it possible to make the column type work like this, or does it only work on the insert statement?

Upvotes: 0

Views: 40

Answers (1)

eshirvana
eshirvana

Reputation: 24603

it's called computed column or generated column feature:

CREATE TABLE `tbl_leaderboard` (
    `leaderboardID` INTEGER NOT NULL AUTO_INCREMENT,
    `totalScore` INTEGER DEFAULT 0 NOT NULL,
    `gamesPlayed` INTEGER DEFAULT 0 NOT NULL,
    `averageScore` DOUBLE as (totalscore /gamesPlayed) ,
    `player_score` INTEGER DEFAULT 0 NOT NULL,
    `userID` INTEGER NOT NULL,
    `playerID` INTEGER NOT NULL,
    PRIMARY KEY (`leaderboardID`),
    FOREIGN KEY (`userID`) REFERENCES `tbl_user`(`userID`)  ON DELETE CASCADE,
    FOREIGN KEY (`playerID`) REFERENCES `tbl_player`(`playerID`) ON DELETE CASCADE
);

and you don;t have to mention it in your update/insert statements , since it automatically will be calculated based on other columns

Upvotes: 3

Related Questions