USE OLAP
--Each level of a dimension table 
SELECT order_method_type,retailer_type --dimensions
       ,sum(quantity_sold) as number_sold --measures
FROM sales
GROUP BY order_method_type,retailer_type --always group by dimensions
ORDER BY number_sold desc --sorting is optional but usually a good idea 
--But if you are using this query for a view you CAN NOT have adn ORDER BY clause

SELECT c.country,c.city --dimensions
       ,sum(s.quantity_sold) as number_sold --measure
FROM countries as c JOIN sales as s ON c.city=s.retailer_city
GROUP BY c.country,c.city 
ORDER BY number_sold desc

SELECT p.product_line,p.product_type --dimensions
       ,sum(s.quantity_sold) as number_sold --measure
FROM sales as s JOIN products as p ON p.code=s.product_code
GROUP BY p.product_line,p.product_type --dimensions
ORDER BY number_sold desc

--2. At specific levels across several dimensions
--number of sales for a given year of a specific product and retailer type
SELECT t.year,c.city,p.product_type,s.retailer_type --dimensions
       ,sum(s.quantity_sold) as number_sold --measure
FROM sales as s JOIN countries as c ON s.retailer_city=c.city
				JOIN products as p ON s.product_code=p.code 
				JOIN times as t ON t.date=s.date
GROUP BY t.year,c.city,p.product_type,s.retailer_type
ORDER BY t.year,c.city,p.product_type,s.retailer_type

--3. Include lots of measures and their aggregates
SELECT retailer_type --dimensions
	   --aggreagates for quantity_sold measure
       ,sum(quantity_sold) as number_sold
	   ,avg(quantity_sold) as average_sold
	   ,count(*) as number_of_sales
	   ,max(quantity_sold) as max_quantity
	   ,min(quantity_sold) as min_qunatity
	   --aggregates for sale_price measure
	   ,sum(sale_price) as revenue
	   ,avg(sale_price) as average_price
	   ,max(sale_price) as highest_price
	   ,min(sale_price) as minimum_price
FROM sales
GROUP BY retailer_type --always group by dimensions
