Chapter 4 Exercise 17

17. By total value, which supplier has the highest sales for the year?

SELECT Supplier.SupplierID, Supplier.Name, Sum([SaleItem].[Quantity]*[SalePrice]) AS SaleTotal
FROM (Merchandise INNER JOIN ((Supplier INNER JOIN MerchandiseOrder
ON Supplier.SupplierID = MerchandiseOrder.SupplierID)
INNER JOIN OrderItem ON MerchandiseOrder.PONumber
= OrderItem.PONumber) ON Merchandise.ItemID = OrderItem.ItemID)
INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID
GROUP BY Supplier.SupplierID, Supplier.Name
ORDER BY Sum([SaleItem].[Quantity]*[SalePrice]) DESC;