--SQL itself has also been enhanced over the years to include
--SQL has also been extended to include anlytical functions using the OVER() clause 
/* Show me the revenue for each retailer type for each month in 2017 
   include a column showing me the percentage across the whole year */
SELECT datename(month,[date]) as month
       ,sum(sale_price*quantity_sold) as revenue
	   --,sum(sum(sale_price*quantity_sold)) over() as grand_total
	   ,sum(sale_price*quantity_sold)/sum(sum(sale_price*quantity_sold)) over()*100 as percent_of_total
FROM sales_2017
GROUP BY datename(month,[date]),datepart(month,[date])
ORDER BY datepart(month,[date])

-- and totals and subtotals using GROUPING SETS
/* Show me the monthly revenue
   include a grand total in the report */
SELECT datename(month,[date]) as month
       ,sum(sale_price*quantity_sold) as revenue
FROM sales_2017
GROUP BY GROUPING SETS ((datepart(month,[date]),datename(month,[date])),())