sql query for multiple row and multiple grouping

Question

im doing a project for learning, and having some question that i need to answer with comparing the data between years 2009 and 2010.

it just containt 1 table with table name data_penjualan

the current query that i write is just can show both of year in vertical.. so its show 2009 first and below it has the 2010 data. is there possible to make the data in vertical like Years | Product_sub_Category|sales 2009 | Sales 2010

select 
extract(year from order_date) as years,
product_sub_category,
sum(sales) as sales

from data_penjualan
where extract(year from order_date) IN ('2009', '2010') and order_status = "Order Finished" 
group by 1,2
order by 1,3 DESC;

this is the picture, sorry for bad english

score 1 · Answer 1 · answered Jul 19 '22 at 15:46

1

The solution for your query could be similar to one of the answer in this post. Convert Rows to columns using 'Pivot' in SQL Server

you can use pivot functionality.

answered Jul 19 '22 at 15:46

VKK

56
7

thank you for the reference, im on learning for similiar solution like that post. i just found the answer when googleing.. that post is more fancy than the solution that i looking for... but thanks.... – Tristianto Raflesia Jul 19 '22 at 16:11

score 0 · Answer 2 · answered Jul 19 '22 at 16:09

i found the answer its actually convert row to column

select product_sub_category,
sum( if(extract(year from order_date) = '2009' , sales,0)) as sales_2009,
sum( if(extract(year from order_date) = '2010', sales,0)) as sales_2010

from data_penjualan
where order_status = "Order Finished"
group by product_sub_category
order by 2 DESC;

sql query for multiple row and multiple grouping

2 Answers2