--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;