1

There are many accountants and each of them has jobs (paid by the hour) and I need to get the accountant name of every accountant who has an average job cost higher than the overall average of job costs. How do I do this?

SELECT Accountant_Name, AVG(job_cost) as 'Average'
FROM job_view
WHERE Average > (SELECT AVG (job_cost) AS AV
FROM job_view)
GROUP BY Accountant_Name;

Everything needed is in a view named the job_view. The above code is not working any help on modifications would be appreciated. Thanks in advance.

Ravindu Gamage
  • 39
  • 8
  • What is the error you are getting with above code? – Kannan Kandasamy Sep 22 '16 at 13:49
  • Invalid column name 'Average' – Ravindu Gamage Sep 22 '16 at 13:50
  • the 'Average' ALIAS for the `AVG(Job_Cost)` column is not visible in the `WHERE` clause. Which is why you could try replacing it with the `AVG(Job_Cost)`, but then you'll get that windowed functions can exist only in `HAVING` and `SELECT` parts of the query. Just take a look at my answer on how to solve all of this. – Radu Gheorghiu Sep 22 '16 at 13:53

2 Answers2

1

This should do it for you:

SELECT Accountant_Name
    , AVG(Job_Cost) as 'Average'
FROM Job_View
GROUP BY Accountant_Name
HAVING AVG(Job_Cost) > (SELECT AVG(Job_Cost) FROM Job_View)

As per your comment, the error you're getting at WHERE Average > is because the alias Average is not visible in a WHERE clause and usually requires you to put the entire contents of the column just as you defined it in the SELECT part.

But because in the SELECT part the Average column is a aggregate function, these can only go lower in the HAVING section, because HAVING handles filtering of aggregate conditions.

Why all this? Because there are rules for order of execution of statements in a query, as explained here.

Community
  • 1
  • 1
Radu Gheorghiu
  • 20,049
  • 16
  • 72
  • 107
  • It worked with the GROUP BY clause added thank you but I tried the same thing with WHERE instead of HAVING then it still gave me a error. Any chance you could tell me why that was? – Ravindu Gamage Sep 22 '16 at 13:55
1

You'll still need to Group by Accountant_Name

SELECT Accountant_Name, AVG(job_cost) as 'Average'
    FROM job_view
    GROUP BY Accountant_Name
    Having AVG(job_cost) > (SELECT AVG (job_cost) FROM job_view);
SS_DBA
  • 2,403
  • 1
  • 11
  • 15