CODEWITHSUNDEEP
mysqlsql
Tharaka
Tharaka

Reputation: 21

How to overcome the error Subquery returns more than 1 row in Mysql

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;

Upvotes: 1

Views: 43

Answers (2)

nbk
nbk

Reputation: 49395

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

Upvotes: 1

vishwampandya
vishwampandya

Reputation: 1187

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);

Upvotes: 5

Related Questions