Как найти максимальное среднее число

Нужно найти, какой артист(artname) имеет максимальную среднюю цену(average cdprice).Есть таблицы

Artist:

CD:

Пробовал написать:

SELECT artname 
FROM cd, artist 
GROUP BY artname 
HAVING (SUM(cdPrice)/COUNT(cd.artid)) >= ALL(SELECT (SUM(cdprice)/COUNT(cd.artid)) FROM cd,artist ar WHERE ar.artid=cd.artid GROUP BY ar.artname);

Но не получилось.

задан 6 ноя 2018 в 14:35

Самый универсальный вариант:

select a.artname
from   (select cd.artid, avg(cd.cdprice) as avg_cdprice
        from cd
        group by cd.artid
        ) c
    join artist a on c.artid = a.artid
where c.avg_price = (select max(avg_price)
                     from (select cd.artid, avg(cd.cdprice) as avg_cdprice
                            from cd
                            group by cd.artid
                            ) cc
                    )

ответ дан 6 ноя 2018 в 15:31

Сам смог разобраться:

SELECT artName FROM Artist, CD WHERE Artist.artID = CD.artID
GROUP BY artName HAVING AVG(cdPrice) >= ALL(SELECT AVG(cdPrice) FROM CD GROUP BY cdGenre);

ответ дан 6 ноя 2018 в 15:36

I am trying to display the maximum average salary; however, I can’t seem to get it to work.

I can get a list of the average salaries to display with:

select worker_id, avg(salary)
from workers
group by worker_id;

However, when I try to display a list of the maximum average salary with:

select max (avg(salary))
from (select worker_id, avg(salary)
      from workers
      group by worker_id);

it doesn’t run. I get an «invalid identifier» error. How do I use the average salary for each worker to find the maximum average for each worker?

Thanks.

asked Nov 8, 2011 at 13:07

select worker_id, avgsal 
from 
(
  select worker_id, avg(salary) as avgsal 
  from workers 
  group by worker_id
) 
where avgsal=(select  max(avgsal) 
              from (select worker_id, avg(salary) as avgsal 
                    from workers group by worker_id))

This will display the highest average along with worker id

As explained here
you can use

SELECT worker_id, AVG(salary)
FROM workers
GROUP BY worker_id
HAVING AVG(salary) = (SELECT MAX(AVG(salary)) FROM workers GROUP BY worker_id) 

answered Apr 26, 2015 at 18:52

select worker_id, avg(salary)
from workers
group by worker_id
having avg(salary) = (select max(avgsal) from 
(select worker_id, avg(salary) as avgsal 
from workers 
group by worker_id));

This should also work i guess

You can fix the query by adding a column alias to the column within the sub-query, like so:

select max(avg_salary)
from (select worker_id, avg(salary) avg_salary
      from workers
      group by worker_id);

However, if worker_id uniquely identifies records on the workers table, this is functionally equivalent to (can be simplified to):

select max(salary) from workers;

answered Nov 8, 2011 at 13:14

using WITH clause it can be done as

with averagesal as (
select dept_id d_id, avg(sal) avgsal from emp_details group by dept_id)
select * from averagesal where avgsal = (select max(avgsal) from averagesal);

answered Nov 24, 2014 at 9:57

select max(a.high)Avg_highest_salary,
       e.dept 
from  (
    select avg(salary) high,dept from emp group by dept) a,
    emp e 
where  a.dept = e.dept
group by   e.dept
order by   max(a.high) desc

It will show the high Average highest salary first with dept

If you don’t want to show the Salary with Dept then you can use this

select max(avg(salary)) max_avg_salary
from emp
group by dept;

You should try the following approach:

select avg(salary) as max_avg_salary from Salaries group by emp_no order by avg(salary) desc limit 1;

https://stackoverflow.com/a/8050885/12190487 shows the folllowing error

ER_DERIVED_MUST_HAVE_ALIAS: Every derived table must have its own alias

Use alias for the new formed column you are selecting from

select max(avg_salary)
from (select worker_id, avg(salary) AS avg_salary
      from workers
      group by worker_id) as avg ; 

answered Oct 9, 2019 at 17:54

This worked out for me.

from (select avg(salary) AS avg_salary
       from employees
      group by Name) AS T;

answered Nov 15, 2019 at 13:11

You can in this way that the first row is sorted in descending based on average find

select top 1  worker_id, avg(salary) as avgsalary 
      from workers
      group by worker_id 
      order by avgsalary desc

select * from (select avg(sal) over (partition by deptno ) avrg,deptno from emp
order by avrg desc) where rownum<2;

Try the above one.

Here is how to also get the worker_id, inspired by previous answers:

SELECT worker_id, MAX(avg_salary)
FROM (SELECT worker_id, AVG(salary) AS avg_salary
      FROM workers
      GROUP BY worker_id);

answered Oct 12, 2020 at 13:55