db 1

gistfile1.sql

WITH ec AS ( SELECT  C.LastName AS Customer
                      , e.LastName AS SalesPerson
                      , s.ProductID
                      , p.Name AS ProductName
                      , SUM(s.Quantity) AS quantity
                      , SUM(p.Price * s.Quantity) AS amount
                      , SUM(SUM(s.Quantity)) OVER (PARTITION BY s.ProductID)*1.0/SUM(COUNT(*)) OVER (PARTITION BY s.ProductID) AS Avg_Qty
                FROM     dbo.Sales s
                    JOIN dbo.Customers c ON s.CustomerID = c.CustomerID
                    JOIN dbo.Employees e ON s.SalesPersonID = e.EmployeeID
                    JOIN dbo.Products p ON s.ProductID = p.ProductID
                WHERE   p.Name LIKE 'Paint%'
                GROUP BY C.LastName
                      , e.LastName
                      , s.ProductID
                      , p.Name
            )
SELECT * 
FROM  ec 
WHERE quantity < Avg_Qty