/* Question #4 - 10 points

	Write a query that brings back all the stock items (in warehouse.StockItems).  Provide
		a column that totals sales for each for 2013, 2014, 2015, and 2016.  Include the
		StockItemName and StockItemID.  Sort the list from largest overall sales to
		the smallest.

	You should get 227 records and 6 columns.  The first 2 row looks like the following:

StockItemID       StockItemName                                       2013Sales            2014Sales            2015Sales                2016Sales
215               Air cushion machine (Blue)                          3612087.90          3688522.65          3815185.95                1657542.15
173               32 mm Anti static bubble wrap (Blue) 50m            1977885.00          2167462.50          2332890.00                863362.50
*/
SELECT
	SI.StockItemID,
	SI.StockItemName,
	SUM(CASE WHEN YEAR(O.OrderDate) = 2013 THEN OL.Quantity * OL.UnitPrice END) [2013Sales],
	SUM(CASE WHEN YEAR(O.OrderDate) = 2014 THEN OL.Quantity * OL.UnitPrice END) [2014Sales],
	SUM(CASE WHEN YEAR(O.OrderDate) = 2015 THEN OL.Quantity * OL.UnitPrice END) [2015Sales],
	SUM(CASE WHEN YEAR(O.OrderDate) = 2016 THEN OL.Quantity * OL.UnitPrice END) [2016Sales]

FROM
	Sales.Orders O
	INNER JOIN Sales.OrderLines OL ON O.OrderID = OL.OrderID
	INNER JOIN Warehouse.StockItems SI ON SI.StockItemID = OL.StockItemID

GROUP BY
	SI.StockItemID,
	SI.StockItemName

ORDER BY
	SUM(OL.Quantity * OL.UnitPrice) DESC