LEJ LEJ - 1 year ago 74
SQL Question

SQL find name of worker with maximum average score

I am trying to display the name of the worker(s) with the largest average marks.
My first table is the worker table and stores the worker_id and worker_name. The second table is the test table and stores the worker_id of who took the test, the test_id, and the marks.

My goal is to be able to print the name of the worker who has the highest average marks across all tests. This is what I have so far:

select max(avg_mark) as max_avg
from (
select worker_name, avg(mark) as avg_mark
from worker join test worker.worker_id = test.worker_id
group by worker_name
order by avg(mark) desc);

However, this only returns the VALUE of the maximum average mark and not the name(s) of the worker(s) who have that average. If I add
to the first select statement and also add a group by
at the bottom then all the workers are returned with their averages!

Answer Source

You are close. The idea is to select the worker who's average matches the max found in a separate query. Here is another one that works:

select master.worker_name, mx.max_mark
(select worker_name, avg(mark) as avg_mark
 from worker
 inner join test on worker.worker_id = test.worker_id
 group by worker_name) master
inner join
(select max(mark) as max_mark
 from test) mx
on master.avg_mark = mx.max_mark
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download