Mysql: SUM/ MAX query

Databases
1

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.

2 

SELECT 
    Score.performerId
  , Performer.performerName
  , Performer.countrycode
  , Country.countryName
  , Event.eventName
  , SUM(Score.points) AS totalpoints
FROM Score
INNER JOIN Performer
ON Score.performerId=Performer.performerId
INNER JOIN Country
ON Performer.countryCode=Country.countryCode
INNER JOIN Event
ON Score.eventId=Event.eventId
WHERE Ccore.roundName = 'Final'
AND   Event.eventName = 'Piano'
GROUP BY Score.performerId
ORDER BY SUM(Score.points) DESC
LIMIT 1
3 
SELECT Performer.performerName
     , Country.countryName
     , SUM(Score.points) AS total_score
  FROM Event
INNER
  JOIN Score
    ON Score.eventId = Event.eventId
   AND Score.roundName = 'Final'
INNER
  JOIN Performer
    ON Performer.performerId = Score.performerId
INNER
  JOIN Country
    ON Country.countryCode = Performer.countryCode
 WHERE Event.eventName = 'Piano'
GROUP
    BY Performer.performerId
ORDER
    BY total_score DESC LIMIT 1

so much prettier, eh guido? :smiley:

important: note which table the FROM clause starts with (hint: look at my WHERE clause)

4

Details… :smiley:

5

Thank you both Gentlemen!
You are both Masters!