CODEWITHSUNDEEP
sqlmysqlselectgroup-byaverage
Ashish
Ashish

Reputation: 14697

using average with group by in mysql

I have three table

EMPLOYEE table with ID and NAME
COMPANY table with ID and NAME
and 
SALARY table with EMPLOYEE_ID, COMPANY_ID and SALARY

I wanted to print the name of every company where the average salary is greater than or equal to 40000

My query is 

Select distinct COMPANY.NAME from COMPANY,SALARY,EMPLOYEE WHERE SALARY.COMPANY_ID=COMPANY.ID and (select avg(SALARY) from SALARY,EMPLOYEE WHERE SALARY.EMPLOYEE_ID=EMPLOYEE.ID) >=40000;

Upvotes: 0

Views: 20705

Answers (3)

Payal Sen
Payal Sen

Reputation: 21

SELECT DISTINCT COMPANY.NAME 
FROM COMPANY 
INNER JOIN SALARY ON COMPANY.ID = SALARY.COMPANY_ID 
INNER JOIN EMPLOYEE ON EMPLOYEE.ID = SALARY.EMPLOYEE_ID 
GROUP BY COMPANY.NAME HAVING AVG(SAL)>=40000

Upvotes: 2

Ajay Singh
Ajay Singh

Reputation: 492

SELECT T1.NAME FROM COMPANY AS T1 INNER JOIN (SELECT COMPANY_ID, AVG(SALARY) AS avgSalary FROM SALARY GROUP BY COMPANY_ID) AS T2 ON T1.ID = T2.COMPANY_ID WHERE T2.avgSalary >= 40000;

Upvotes: 0

Mureinik
Mureinik

Reputation: 311073

You could group by the company name and have the condition in the having clause:

SELECT   c.name
FROM     company c
JOIN     salary s ON c.id = s.company_id
GROUP BY c.name
HAVING   AVG(salary) >= 40000

Upvotes: 11

Related Questions