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.

https://stackoverflow.com/questions/66620081/how-to-select-the-average-salary-of-specific-contenders-in-sql March 14, 2021 at 08:58AM