PostgreSQL – Group By Transformed Column

group bypostgresql

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 yearcrea and monthcrea columns:

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 ?

Best Answer

You can simplify further:

SELECT date_trunc('month', c.created_at) AS year_month
     , p.reference,
     , count (distinct c.id) as nb_crea,
     , (sum(o.amount_ati_cents) / 100) as ca_euros
from   creations   c
join   products    p on p.id = c.product_id 
join   order_items i on i.creation_id = c.id
join   bundles     b on b.id = i.bundle_id
join   orders      o on o.id = b.order_id
where  o.status NOT IN ('canceled', 'new')
and    c.name like '% - Premium(%'
and    p.reference like 'FLA_%'
GROUP BY year_month, reference
ORDER BY year_month DESC;

Or even:

SELECT to_char(c.created_at, 'YYYY-MM') AS year_month
...
GROUP  BY 1, reference
ORDER  BY 1 DESC;

date_trunc() and to_char() in the manual.

Related Question