SUM/ MAX query

Julian Mishke · #1 (**permalink**) 03-15-11, 22:13

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.

it-iss.com · #2 (**permalink**) 03-16-11, 04:21

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)

r937 · #3 (**permalink**) 03-16-11, 06:35

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

Julian Mishke · #4 (**permalink**) 03-16-11, 16:54

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!

it-iss.com · #5 (**permalink**) 03-16-11, 17:01

Try the following:

r937 · #6 (**permalink**) 03-16-11, 17:08

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 )

it-iss.com · #7 (**permalink**) 03-16-11, 17:14

Rudy as much as I think I know SQL you are just on another level!!

r937 · #8 (**permalink**) 03-16-11, 17:32

thanks ronan, appreciate it

you're not so bad yourself, babe

Julian Mishke · #9 (**permalink**) 03-16-11, 18:00

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!

Julian Mishke · #10 (**permalink**) 03-16-11, 18:22

*** Thank you!!!

r937 · #11 (**permalink**) 03-16-11, 18:26

Quote:

Originally Posted by Julian Mishke

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

And what does ‘t’ mean at the end?

it's a table alias and is required for subqueries in the FROM clause