SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    mysql: SUM/ MAX query

    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. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Code:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,022
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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?

    important: note which table the FROM clause starts with (hint: look at my WHERE clause)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,412
    Mentioned
    149 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    so much prettier, eh guido?

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

  5. #5
    SitePoint Member
    Join Date
    Mar 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you both Gentlemen!
    You are both Masters!


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •