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.
Do you just want
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.