I am trying to find the employees who have sales figures that are above the average sales totals for a particular company. I am using Microsoft SQL Server, and here is what I have so far:
SELECT * FROM ( SELECT Name, SalesTotal as Sales, AVG(SalesTotal) as MeanSales FROM Employees GROUP BY Name SalesTotal ) nested WHERE Sales > MeanSales
However, my query returns nothing. Is this the correct way to write something like this? What could I do to simplify things?
Advertisement
Answer
If you have 1 row for each employee in the table then use a subquery in the WHERE
clause which returns the average:
SELECT Name, SalesTotal AS Sales FROM Employees WHERE SalesTotal > (SELECT AVG(SalesTotal) FROM Employees)