--Analytical Queries
USE OLTP --On our relational model
SELECT order_method_type, count(*) as number_of_sales 
FROM sales
GROUP BY order_method_type

USE OLTP --On our relational model
SELECT c.country,s.order_method_type,p.product_line
       ,sum(s.quantity_sold) as quantity_sold
FROM sales as s JOIN products as p ON s.product_code = p.code
                JOIN countries as c ON s.retailer_city=c.city
GROUP BY c.country,s.order_method_type,p.product_line
ORDER BY c.country,s.order_method_type,p.product_line

--partitioning.
USE OLAP --On our analytical model
--2017 Sales by Order Method Type
SELECT order_method_type, count(*) as number_of_sales 
FROM sales_2017
GROUP BY order_method_type
--Number of rows for each year
SELECT '2016' as year, count(*) as number_of_rows
FROM sales_2016
UNION ALL
SELECT '2017', count(*)
FROM sales_2017

--number of character used without separate table
SELECT 120 --number of characters in order_method_type 
       *((SELECT count(order_method_type) FROM sales_2016)
          +(SELECT count(order_method_type) FROM sales_2017))
	   as total_number_of_characters_using_one_table

--number of character used with separate table
SELECT count(*) --number of rows
       *121 --number of characters in order_method_type_table
	   +1 --number of characters in order_method_type_pk
	   *((SELECT count(order_method_type) FROM sales_2016)
          +(SELECT count(order_method_type) FROM sales_2017)) --number of rows in sales
	   as total_number_of_characters_using_two_tables