Hello,
I am very new to this and having problems with the SUM and MAX in my query:
SELECT Score.performerId, Performer.performerName, Performer.countrycode, Country.countryName, Event.eventName, Score.points
FROM Score, Performer, Country, Event
WHERE points = (SELECT MAX(points) FROM Score WHERE roundName = 'Final') AND roundName = 'Final'
AND Score.performerId=Performer.performerId
AND Performer.countryCode=Country.countryCode
AND Score.eventId=Event.eventId
The tables look like this:
Performer (performerID, performerName, countryCode)
Assessor (assessorID, aName, countryCode)
Event (eventID, eventName)
Round (eventID, roundName, roundDate, roundTime)
Score (eventId, roundName, performerID, assessorId, points)
PerformerEvent (performerID, eventID)
There are five assessors in the Final stage and the query is supposed to show the winner of the competition- the person who got the highest scores as a sum of the 5 scores in the final round. There are 2 finalists. I don't really know what to do with the SUM function- where it belongs. The MAX statement is incorrect.
Also, just realised that the name of the event is missing from my script- it is supposed to be eventName = 'Piano'.
I will greatly appreciate any help.