Results 1 to 11 of 11

Thread: SUM/ MAX query

  1. #1
    Join Date
    Mar 2011
    Posts
    4

    Unanswered: SUM/ MAX query

    Hello,

    I am very new to this and having problems with the SUM and MAX in my query:


    WHERE points = (SELECT MAX(points) FROM Score WHERE roundName = '***') AND roundName = '***'


    I will greatly appreciate any help.
    Last edited by Julian Mishke; 03-16-11 at 19:34.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi,

    you mention "the person who got the highest scores as a sum of the 5 scores in the final round"

    So I think this statement is not strictly correct:

    SELECT MAX(points) FROM Score WHERE roundName = 'Final'

    Try something like

    Code:
    SELECT Max(performerpoints)
    FROM   (SELECT SUM(points) AS performerpoints
            FROM   score
            WHERE  roundname = 'Final'
            GROUP  BY performerid)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2011
    Posts
    4

    MAX points

    Thank you both very much!

    Rudy, your query works like a dream the only problem is- I have few winners. Would it be possible to remove the limit and just get it to show any number of highest scorers?

    I tried removing the LIMIT 1 and it's just showing me the list of all the performers in the round of the event. If it was only that simple...

    Big thank you in advance!
    Last edited by Julian Mishke; 03-16-11 at 19:34.

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Try the following:
    Last edited by it-iss.com; 03-16-11 at 18:06. Reason: The code that I originally posted was not correct.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ronan, looks like you forgot the 'Piano' event condition

    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
    HAVING total_score =
           ( SELECT MAX(subtotal)
               FROM ( SELECT Score.performerId
                           , SUM(Score.points) AS subtotal
                        FROM Event
                      INNER
                        JOIN Score
                          ON Score.eventId = Event.eventId
                         AND Score.roundName = 'Final'
                       WHERE Event.eventName = 'Piano'
                      GROUP
                          BY Score.performerId ) AS t )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Rudy as much as I think I know SQL you are just on another level!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks ronan, appreciate it

    you're not so bad yourself, babe

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Mar 2011
    Posts
    4
    Thank you soooo very much! You both are Real Masters! Hats off!
    I would love to learn from you.

    I am trying to understand your code Rudy. Could I please, ask you:

    why are you starting from Event table (right after the first SELECT command)?
    And what does ‘t’ mean at the end?

    Massive 'Thank You' for all your time and advice!
    Last edited by Julian Mishke; 03-17-11 at 16:22.

  10. #10
    Join Date
    Mar 2011
    Posts
    4
    *** Thank you!!!
    Last edited by Julian Mishke; 03-17-11 at 16:22.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Julian Mishke View Post
    why are you starting from Event table
    because that's likely the "driving" table which the optimizer will pick as the most restrictive table (see the WHERE clause) -- thus returning fewer rows to be joined, as opposed to starting with, say, the score table, returning all scores and then throwing away the ones that aren't for the right event

    Quote Originally Posted by Julian Mishke View Post
    And what does t mean at the end?
    it's a table alias and is required for subqueries in the FROM clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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