how join 2 columns and extract year and count postgresql

user_16459293 0
Viewed 61

I have this table

enter image description here

I need to know the most popular color every year

I just know how to join this table with this code, I don't know how to extract the year and count the color

select FS.order_date, FS.quantity, DP.color
from fact_sales FS
join dim_product DP
on FS.product_id = DP.product_id

and give me the example result

order_date  quantity    color 
2016-01-07  2           Silver 
2016-02-07  2           Black
2016-02-07  1           Silver 
2017-05-07  1           Black 
2017-05-07  4           Red 
2017-06-07  2           Black
count join postgresql sql
1 Answers

You need to count the total quantity by year and then pare down to one year. The first part is:

select date_trunc('year', FS.order_date) as yyyy, DP.color, sum(FS.quantity) as quantity
from fact_sales FS join
     dim_product DP
     on FS.product_id = DP.product_id
group by yyyy, color;

For the second part, you can use a Postgres extension, distinct on:

select distinct on (date_trunc('year', FS.order_date)), date_trunc('year', FS.order_date) as yyyy, DP.color, sum(FS.quantity) as quantity
from fact_sales FS join
     dim_product DP
     on FS.product_id = DP.product_id
group by yyyy, color
order by yyyy, quantity desc;
user_11440350
Related