--select data
select * from sales3 order by 1,2;


--pivot on the area column to produce a result that shows sales and units sold for each area by year
select *from (select f_yr, area, units, sales_amt from sales3) dt
	PIVOT
	(sum(sales_amt) as sales, sum(units) as units for area in (select distinct area from sales3)) tmp
order by 1;


--pivot on the area column to produce a result that shows sales and units sold for each area by year
--and another column that sums the generated area sales into another column called all_area_sales sales
select * from (select f_yr, area, units, sales_amt from sales3) dt
	PIVOT
	(sum(sales_amt) as sales, sum (units) as units 
	 for area in (select area from sales3 group by 1)
	 with sum("east_sales", "west_sales", "south_sales", "north_sales") as all_area_sales) tmp
where f_yr in (select f_yr from sales3 group by 1)
order by 1;


--pivot on the area column to produce a result that shows sales and units sold for each area by year and quarter
--and another column that sums the generated area sales into another column called all_area_sales sales

select * from (select f_yr, area, units, sales_amt from sales3) dt
	
	PIVOT	(sum(sales_amt) as sales, sum(units) as units
		for area in ('east' as "east", 'west' as "west", 'north' as "north", 'south' as "south")
		with sum("east_sales", "west_sales", "south_sales", "north_sales") as all_area_sales) tmp
where f_yr in (select f_yr from sales3 group by 1)
order by 1;