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

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);
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.