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;

user1822 · Accepted Answer · 2018-03-22 08:12:21Z

2

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

answered Mar 22, 2018 at 8:12

user1822

1 Answer 1