USE OLAP
--Adding measures using calculations
SELECT --dimensions
       order_method_type
	   ,retailer_type 

	   --measures based on calculations
	   ,sum(sale_price*quantity_sold) as revenue 

       --measures for sale price
	   ,min(sale_price) as min_sale_price
	   ,max(sale_price) as max_sale_price
	   ,avg(sale_price) as average_sale_price

	   --measures for quantity sold
       ,sum(quantity_sold) as number_sold
	   ,count(quantity_sold) as number_of_sales 

FROM sales
GROUP BY order_method_type,retailer_type --always group by dimensions

--We can also base our measure on a condition.
SELECT --dimension
       order_method_type
	   ,retailer_type 

	   --measures based on condition
	   ,count(CASE WHEN urgent='YES' THEN 1 END) as urgent_orders

	   --measures based on calculations
	   ,sum(sale_price*quantity_sold) as revenue

       --measures for sale price
	   ,min(sale_price) as min_sale_price
	   ,max(sale_price) as max_sale_price
	   ,avg(sale_price) as average_sale_price

	   --measures for quantity sold
       ,sum(quantity_sold) as number_sold
	   ,count(quantity_sold) as number_of_sales 

FROM sales
GROUP BY order_method_type,retailer_type

--Finally we might want to base our measure on uniqueness.

SELECT --dimensions
       order_method_type
	   ,retailer_type 

	   --measures based on distinct
	   ,count(distinct product_code) as unique_products
	   ,count(*) - count(distinct product_code) as repeat_sales

	   --measures based on condition
	   ,count(CASE WHEN urgent='YES' THEN 1 END) as urgent_orders

	   --measures based on calculations
	   ,sum(sale_price*quantity_sold) as revenue

       --measures for sale price
	   ,min(sale_price) as min_sale_price
	   ,max(sale_price) as max_sale_price
	   ,avg(sale_price) as average_sale_price

	   --measures for quantity sold
       ,sum(quantity_sold) as number_sold
	   ,count(quantity_sold) as number_of_sales 

FROM sales
GROUP BY order_method_type,retailer_type