I have a database with creations and orders, and a bunch of tables linking them.
I want to extract, for each month, the number of ordered creations, and the sum of amount for these orders, but for a specific creation name pattern, and a specific type of product references.
I have this, which doesn't work because the inner query does not knows the
select date_part('year', c.created_at) as yearcrea, date_part('month', c.created_at) as monthcrea, p.reference, count (*) as nb, ( select sum(o2.amount_ati_cents) from creations as c2 inner join products as p on c.product_id = p.id inner join order_items as oi2 on c2.id = oi2.creation_id inner join bundles as b2 on b2.id = oi2.bundle_id inner join orders as o2 on o2.id = b2.order_id where o2.status NOT IN ('canceled', 'new') and c2.name like '% - Premium(%' and p2.reference like 'FLA_%' and date_part('year', c2.created_at) = yearcrea and date_part('month', c2.created_at) = monthcrea ) as ca from creations as c inner join products as p on c.product_id = p.id where c.name like '% - Premium(%' and p.reference like 'FLA_%' and c.id in ( select oi.creation_id from order_items as oi inner join bundles as b on b.id = oi.bundle_id inner join orders as o on o.id = b.order_id where o.status NOT IN ('canceled', 'new') ) group by yearcrea, monthcrea, reference order by yearcrea desc, monthcrea desc
I have also tried
and date_part('year', c2.created_at) = date_part('year', c.created_at) and date_part('month', c2.created_at) = date_part('month', c.created_at)
but I get an error:
subquery uses ungrouped column "c.created_at" from outer query.
How can I filter a select sub-query with "transformed" columns ?
Maybe I'm looking at it the wrong way and there is a simpler way to extract the info I want ?