CODEWITHSUNDEEP
mysqlsqldatabase
beau8008
beau8008

Reputation: 49

Query to check the average against a table and return a result if it is greater

I am writing a query where I take the average salary of one company and then check it against the the salaries of the other companies and if it is greater, return that company name as a result. What I have is the two separate parts. I have tried a derived table, a subquery, but I'm new to SQL and I can't figure out what I need to link them.

SELECT AVG(salary), company_name
FROM works
WHERE company_name = 'first_bank_corp';

SELECT salary,company_name
FROM works
WHERE salary > 'first_bank_corp';

Upvotes: 0

Views: 20

Answers (1)

Jordi Llull
Jordi Llull

Reputation: 810

As you said, you just have to use a subquery ;)

  SELECT company_name,
         AVG(salary) AS avg_salary             
    FROM works
   WHERE avg_salary > (
             SELECT AVG(salary) 
               FROM works 
              WHERE company_name = 'first_bank_corp' 
         )
GROUP BY company_name

Upvotes: 1

Related Questions