db

gistfile1.sql

drop table promedio_cantidad 
 
create table promedio_cantidad
(
Customer varchar(100) null,
SalesPerson varchar(100) null,
ProductID int null,
ProductName varchar(100) null,
quantity float null,
amount float null
) 

INSERT INTO promedio_cantidad (Customer,SalesPerson,ProductID,ProductName,quantity, amount)
SELECT
C.LastName as Customer , e.LastName as SalesPerson,
s.ProductID, p.Name, sum ( s.Quantity ) ,
sum ( p.Price * s.Quantity )
FROM dbo.Sales s
inner join Customers c
on s.CustomerID = c.CustomerID
inner join Products p
on s.ProductID = p.ProductID
inner join Employees e
on s.SalesPersonID = e.EmployeeID
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 )

select * from promedio_cantidad where ProductName like 'Paint%'