CODEWITHSUNDEEP
sqlpostgresqljoincount
Napier
Napier

Reputation: 227

how join 2 columns and extract year and count postgresql

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

Upvotes: 1

Views: 155

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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;

Upvotes: 1

Related Questions