USE OLAP
--sales by year and month
SELECT year,month,total_sales
FROM sales_by_year_and_month
ORDER BY year,month

--Can not display data a different levels 
SELECT year,month,total_sales
       ,sum(total_sales) as grand_total
FROM sales_by_year_and_month
ORDER BY year,month

--subquery to calculate the grand_total
SELECT year,month,total_sales
       ,(select sum(total_sales) from sales_by_year_and_month) as grand_total
FROM sales_by_year_and_month
ORDER BY year,month

--Over Clause
SELECT year,month,total_sales
       ,sum(total_sales) over() as grand_total
FROM sales_by_year_and_month
ORDER BY year,month

--sales as a percentage of the grand_total
SELECT year,month,total_sales
       ,sum(total_sales) over() as grand_total
	   ,total_sales/(sum(total_sales) over())*100 as percentage
FROM sales_by_year_and_month
ORDER BY year,month

--Average of the sales
SELECT year,month,total_sales
       ,avg(total_sales) over() as average_sales
	   ,total_sales-(avg(total_sales) over()) as variance
FROM sales_by_year_and_month
ORDER BY year,month