USE OLAP
--Costs by product line
SELECT p.product_line,sum(s.total_cost) as cost
FROM sales_enriched as s JOIN products as p ON s.product_code=p.code
GROUP BY p.product_line

--Include the grand_total
SELECT p.product_line
       ,sum(s.total_cost) as cost
       ,sum(sum(s.total_cost)) over() as grand_total
FROM sales_enriched as s JOIN products as p ON s.product_code=p.code
GROUP BY p.product_line

--% of the total
SELECT p.product_line
       ,sum(s.total_cost) as cost
       ,sum(sum(s.total_cost)) over() as grand_total
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*100 as percent_total
FROM sales_enriched as s JOIN products as p ON s.product_code=p.code
GROUP BY p.product_line

--Fraction or degree of the circle
SELECT p.product_line
       ,sum(s.total_cost) as cost
       ,sum(sum(s.total_cost)) over() as grand_total
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*100 as percent_total
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over()) as fraction
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*360 as degrees
FROM sales_enriched as s JOIN products as p ON s.product_code=p.code
GROUP BY p.product_line

--Check the data
WITH pie_graph_data as
	(SELECT p.product_line
		   ,sum(s.total_cost) as cost
		   ,sum(sum(s.total_cost)) over() as grand_total
		   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*100 as percent_total
		   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over()) as fraction
		   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*360 as degrees
	FROM sales_enriched as s JOIN products as p ON s.product_code=p.code
	GROUP BY p.product_line)
SELECT product_line,cost,sum(grand_total) as grand_total
       ,sum(percent_total) as percent_total,sum(fraction) as fraction,sum(degrees) as degrees
FROM pie_graph_data
GROUP BY GROUPING SETS ((product_line,cost),())

--Doesn't work:
SELECT p.product_line
       ,sum(s.total_cost) as cost
       ,sum(sum(s.total_cost)) over() as grand_total
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*100 as percent_total
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over()) as fraction
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*360 as degrees
FROM sales_enriched as s JOIN products as p ON s.product_code=p.code
GROUP BY GROUPING SETS ((p.product_line),())

--Format Data
SELECT p.product_line
       ,format(sum(s.total_cost),'c') as cost
       ,format(sum(sum(s.total_cost)) over(),'c') as grand_total
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*100 as percent_total
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over()) as fraction
	   ,(sum(s.total_cost)/sum(sum(s.total_cost)) over())*360 as degrees
FROM sales_enriched as s JOIN products as p ON s.product_code=p.code
GROUP BY p.product_line