James B James B - 3 months ago 7
SQL Question

Find average score from event at specific meeting

I need to find the average score from everyone that took part in a high jump event at a specific event (ex event01)

My tables are MEETING, EVENT, RANK and my broken code is below:

SELECT *
FROM(
SELECT M.MEETING_ID, M.MEETING_NAME, E.EVENT_ID, E.EVENT_NAME,
ROW_NUMBER() OVER (ORDER BY R.RESULT) RN, (SELECT AVG(RESULT) FROM RANK WHERE E.EVENT_NAME = 'HIGH JUMP' AND M.MEETING_ID = 'MEET0002' ) AS HG

FROM MEETING M JOIN RANK R ON M.MEETING_ID = R.MEETING_ID
JOIN EVENT E ON E.EVENT_ID = R.EVENT_ID


)

WHERE RN = 1


Expected outcome:

MEETING_ID MEETING_NAME EVENT_ID EVENT_NAME AVERAGESCORE
meet01 sports day event01 high jump 10.6


Really appreciate any help!

Answer

This is just an aggregation query with filtering:

SELECT M.MEETING_ID, M.MEETING_NAME, E.EVENT_ID, E.EVENT_NAME,
       AVG(RESULT) AS HG
FROM MEETING M JOIN
     RANK R
     ON M.MEETING_ID = R.MEETING_ID JOIN
     EVENT E
     ON E.EVENT_ID = R.EVENT_ID  
WHERE E.EVENT_NAME = 'HIGH JUMP' AND M.MEETING_ID = 'MEET0002' 
GROUP BY M.MEETING_ID, M.MEETING_NAME, E.EVENT_ID, E.EVENT_NAME;

You need the GROUP BY to include the first four columns in the result set.

Comments