multiple groupings in select clause in postgres 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;
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);