Query Optimization, (subquery) (sql-transact)

Question

This is the code I have to optimize, I managed to get it to half of the starting cost only with indexes and changing the LIKE for a SUBSTRING sentence. My problem is now with the sub-query in the last line, and the SUM lines in the select, I believe I have to get rid of those by creating a new table or column but can't get it done.

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 
FROM dbo.Sales s, dbo.Customers c, dbo.Employees e, dbo.Products p 
WHERE 
 s.CustomerID = c.CustomerID and 
 s.ProductID = p.ProductID and 
 s.SalesPersonID = e.EmployeeID and
 p.Name like 'Paint%' 
GROUP BY C.LastName , e.LastName , s.ProductID, p.Name 
HAVING sum ( s.Quantity ) < 
(select AVG(s2.Quantity) from dbo.Sales s2 where s2.ProductID=s.ProductID ) 

Any help is welcome, thanks in advance.

Answer 1

Can't test it right now, but I think this should work:

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 
  FROM dbo.Sales s, 
  JOIN dbo.Customers c
    ON c.CustomerID = s.CustomerID 
  JOIN dbo.Employees e
    ON e.EmployeeID = s.SalesPersonID
  JOIN dbo.Products p 
    ON p.ProductID = s.ProductID
   AND p.Name like 'Paint%'
  JOIN (SELECT ProductID,
               AVG(Quantity) as avg_Quantity
          FROM dbo.Sales) s2 
    ON s2.ProductID = s.ProductID
 GROUP BY c.LastName , e.LastName , s.ProductID, p.Name 
HAVING sum(s.Quantity) <  AVG(s2.avg_Quantity)