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

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

select f_yr,"q1_sales", "q2_sales", "q3_sales", "q4_sales","yrly_sales"
from (select f_yr, f_qtr, sales_amt from sales3) dt
	PIVOT
	(
		sum(sales_amt) as sales for f_qtr in ('1' as Q1, '2' as Q2, '3' as Q3, '4' as Q4)
		with sum("Q1_sales", "Q2_sales", "Q3_sales", "Q4_sales") as yrly_sales) tmp
order by 1,2,3,4,5,6;

--pivot on the quarter column to produce a result that shows sales for each quarter by year and area
--and other columns that summarize sales for the year, and first and second half of the year.

select *
from (select f_yr, f_qtr, sales_amt from sales3) dt
	PIVOT
	(sum(sales_amt) as sales for f_qtr in ('1' as Q1, '2' as Q2, '3' as Q3, '4' as Q4)
		with sum("Q1_sales", "Q2_sales", "Q3_sales", "Q4_sales") as yrly_sales,
			   sum("Q1_sales", "Q2_sales") as H1_sales,
			   sum("Q3_sales", "Q4_sales") as H2_sales) tmp
order by 1,2,3,4,5,6;