Skip to content
Advertisement

Query to Identify Above Average Sales for Company

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)
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement