MySQL Forums
Forum List  »  Newbie

SELECT SUM() with only top 4 results
Posted by: Paul Whitaker
Date: March 27, 2020 06:58AM

I have a table of scores with 5 scores each for every tray_id. I want to be able to drop the lowest score for each tray_id. To do this for one tray_id at a time I use this query:

SELECT tray_id,
(SELECT SUM(score)
FROM (SELECT score
FROM scores
WHERE tray_id = 1267
ORDER BY score DESC
LIMIT 4) r) AS the_score
FROM scores

That will pull the correct score for tray 1267. Here is what I am trying to do:

SELECT tray_id AS tray,
(SELECT SUM(score)
FROM (SELECT score
FROM scores
WHERE tray_id = tray
ORDER BY score
LIMIT 4) r) AS the_score
FROM scores
WHERE tray_id IN (1267,1235,1266)
GROUP BY tray_id

It tells me "unknown column 'tray' in where clause"

How do I get that tray_id in the query?

Options: ReplyQuote


Subject
Written By
Posted
SELECT SUM() with only top 4 results
March 27, 2020 06:58AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.