I want to find all employee names who earn more than average salary of all employees of their company.
This is the database
create table works(
employee_name varchar (100) primary key,
company_name varchar (100),
salary int (6) );
This is the code I used,
select employee_name
from works
where salary > (select avg(salary) from works group by company_name)
group by company_name;
Instead of a group by in the subquery, you just need a where clause:
SELECT employee_name
FROM works w
WHERE salary > (select avg(salary) from works w2 where w2.company_name = w.company_name);
You don't need a group by in the outer select, but you have to add a where clause to the inner select.
create table works( employee_name varchar (100) primary key, company_name varchar (100), salary int (6) );
INSERT INTO works VALUES ('a','comp A',100),('a2','comp A',150),('a3','comp A',250),('a4','comp B',100) ,('a5','comp B',300),('a6','comp B',200)
select employee_name,company_name,salary from works w1 where salary >= (select avg(salary) avgsal from works w2 WHERE w1.company_name = w2.company_name group by company_name ) ;employee_name | company_name | salary :------------ | :----------- | -----: a3 | comp A | 250 a5 | comp B | 300 a6 | comp B | 200
select avg(salary) avgsal from works w2 group by company_name| avgsal | | -------: | | 166.6667 | | 200.0000 |
db<>fiddle here