Q:

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;

A:

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);

