Mysql – How to calculate the average weight of riders by competition

MySQL

I have an assignment where I need to calculate the average Weight of Riders by Competition. The assignment has a lot of different parts but I got stuck on this one so please help my out if you can.

I have the following tables:

CREATE TABLE Rider(
    name VARCHAR(30),
    weight REAL,
    PRIMARY KEY(name)
) ENGINE=innodb;

CREATE TABLE Participants(
    competition VARCHAR(30) NOT NULL,
    rider VARCHAR(30) NOT NULL,
    PRIMARY KEY(competition, rider),
    FOREIGN KEY(competition) REFERENCES Competition(name),
    FOREIGN KEY(rider) REFERENCES Rider(name)
) ENGINE=innodb;

CREATE TABLE Competition(
    name VARCHAR(30),
    PRIMARY KEY(name)
) ENGINE=innodb;

With the following data:

--Riders
Name : Weight
----   ------
Alan : 55
David : 60
Peter : 65
Stan : 60


--Competition
Name
-----
Derby
York


--Participants
Competition : Rider
-----------   -----
Derby : Alan
Derby : David
York : Alan
York : Peter
York : Stan

So the results I should get from my SQL query for the different competitions should be:

  • Derby: 57,5 (average of Alan and David)
  • York: 60 (average of Alan, Peter and Stan)

I've tried multiple queries. At the moment my best is:

SELECT Competition.name, AVG(Rider.weight) 
FROM Rider, Compeition, Participants 
GROUP BY Competition.name;

How should I tackle this SQL query?

Best Answer

You need to join the rider and participants tables together using the common column (name->rider), group all of the participants for a given competition together, and calculate the average, like so: 

select competition, avg(weight) as average_weight
from participants
join rider on rider.name = participants.rider
group by competition
;

Fiddle here.

Related Question