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:
x
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)