multiple groupings in select clause in postgres

Question

I have the following orders table in a postgres 9.6 db:

orders 
item_id, created_at (a timestamp ie Rails app)

I'd like to get back a list of item_ids and distinct years and a count like this:

item_id year count
------- ---- -----
23 2017 23
24 2017 45 
24 2018 11 

How would I do this? I tried something like this but am stuck:

select item_id, extract(year from created_at) from orders where group by item_id, created_at;

Answer 1

You were nearly there, but you need to group by the year, not the timestamp value:

select item_id, extract(year from created_at), count(*)
from orders 
where ....
group by item_id, extract(year from created_at);