How to select the average salary of specific contenders in SQL

I have a database that stores the payment information of a fictitious TV show. I’m trying to write a select query that lists the stage name of the contenders with the highest total daily salary. Each contender can be made up of one or more participants each participant has a daily salary.

Here are the Contender and Participant tables.

CREATE TABLE Contender (
    stageName       VARCHAR(20)         NOT NULL,
    `type`          BOOLEAN             NOT NULL,
    idContender     INTEGER UNSIGNED    NOT NULL    AUTO_INCREMENT,
    coach           INTEGER UNSIGNED    NOT NULL,
    PRIMARY KEY (idContender),
    FOREIGN KEY (coach) REFERENCES Coach (idCoach)
);

CREATE TABLE Participant (
    `name`          VARCHAR(15)         NOT NULL,
    surname         VARCHAR(20)         NOT NULL,
    DoB             DATE                NOT NULL,
    idParticipant   INTEGER UNSIGNED    NOT NULL    AUTO_INCREMENT,
    phone           VARCHAR(11),
    dailySalary     INTEGER,
    gender          VARCHAR(6),
    contender       INTEGER UNSIGNED    NOT NULL,
    PRIMARY KEY (idParticipant),
    FOREIGN KEY (contender) REFERENCES Contender (idContender)
);

Here is query so far. At the moment it just selects the average salary of all participants. It needs to select the average daily salary of the participants that make up each contender.

    SELECT Contender.stageName, AVG(Participant.dailySalary)
    FROM Contender
    INNER JOIN Participant ON Participant.contender = Contender.idContender;

I’m not quite sure where to go from here. Any help would be very much appreciated.

Answer

Do you just want GROUP BY?

SELECT c.stageName, AVG(p.dailySalary)
FROM Contender c JOIN
     Participant p
     ON p.contender = c.idContender
GROUP BY c.stageName, c.idContender;

Your question is not clear as to whether or not the stageName is unique, so I included the id as well as the name.

Your query should fail with a syntax error — and it would in most databases including the most recent versions of MySQL. The query is an aggregation query because of the AVG(). However, the unaggregated columns in the SELECT are not in a GROUP BY clause.

Leave a Reply

Your email address will not be published. Required fields are marked *