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'
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.
, SUM(Score.points) AS totalpoints
INNER JOIN Performer
INNER JOIN Country
INNER JOIN Event
WHERE Ccore.roundName = 'Final'
AND Event.eventName = 'Piano'
GROUP BY Score.performerId
ORDER BY SUM(Score.points) DESC
, SUM(Score.points) AS total_score
ON Score.eventId = Event.eventId
AND Score.roundName = 'Final'
ON Performer.performerId = Score.performerId
ON Country.countryCode = Performer.countryCode
WHERE Event.eventName = 'Piano'
BY total_score DESC LIMIT 1
so much prettier, eh guido?
important: note which table the FROM clause starts with (hint: look at my WHERE clause)
Thank you both Gentlemen!
You are both Masters!