USE OLAP
--Using cube for totals in our source data
SELECT c.country --null country means it's an order method total
       ,s.order_method_type --null order method type means it's a country total
	   ,SUM(s.revenue) as revenue
FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
GROUP BY CUBE(country,order_method_type) 

--Pivoting 1st attempt
SELECT country --column for our country dimension
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
	   ,[Country Total] --Country total will be an extra column at end of query
FROM (SELECT c.country,s.order_method_type
			 ,SUM(s.revenue) as revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
	  GROUP BY CUBE(country,order_method_type) ) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit],[Country Total]) 
	   --for every order method type listed above
	   --this should correspond to the headings we d,efined in the select statement above.
	   ) as pivot_table

--Pivoting 2nd attempt
SELECT country --column for our country dimension
	   ,[Web],[E-mail],[Mail],[Telephone],[Sales visit] --headings for our order method types
	   ,[Country Total] --Country total will be an extra column at end of query
FROM (SELECT c.country,coalesce(s.order_method_type,'Country Total') as order_method_type
			 ,SUM(s.revenue) as revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
	  GROUP BY CUBE(country,order_method_type) ) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit],[Country Total]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table


--Improving output
SELECT country --column for our country dimension
	   ,format([Web],'c') as [Web] --format as currency
	   ,format([E-mail],'c') as [E-mail]
	   ,format([Mail],'c') as [Mail]
	   ,format([Telephone],'c') as [Telephone]
	   ,format([Sales visit],'c') as [Sales visit]  --headings for our order method types
	   ,format([Country Total],'c') as [Country Total]  --Country total will be an extra column at end of query
FROM (SELECT coalesce(c.country,'Order Method Total') as country
             ,coalesce(s.order_method_type,'Country Total') as order_method_type
			 ,SUM(s.revenue) as revenue
      FROM sales_enriched as s JOIN countries as c ON s.retailer_city=c.city
	  GROUP BY CUBE(country,order_method_type) ) as original_query
PIVOT (sum(revenue)--sum the revenue
	   FOR order_method_type IN ([Web],[E-mail],[Mail],[Telephone],[Sales visit],[Country Total]) 
	   --for every order method type listed above
	   --this should correspond to the headings we defined in the select statement above.
	   ) as pivot_table
ORDER BY case when country='Order Method Total' then 2 --order method type should be the last row in our query
              else 1 --put all other rows first
		 end
		 ,[Country Total] desc --and sort them by country_total in descending order