0

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 ?

1
  • I removed the [plpgsql] tag. This simple SQL, no need for the plpgsql. Mar 6 '15 at 15:06
2

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.

0
1

yearcrea and monthcrea does not exists at the time when the sub-select is evaluated. The following should work:

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 do you really need creations as c2 and products as p2 in the sub-select? Should not:

(
    select sum(o2.amount_ati_cents)
    from order_items as oi2 
    join bundles as b2 
        on b2.id = oi2.bundle_id
    join orders as o2 
        on o2.id = b2.order_id
    where o2.status NOT IN ('canceled', 'new')
      and c.id = oi2.creation_id
) as ca

be sufficient?

Without investigating it more closely, it looks as you could transform the sub-selects in the in clause and the sum to a join at the outer level.

6
  • With the first suggestion (which I tried but forgot to mention), I get subquery uses ungrouped column "c.created_at" from outer query. Yes I need to make sure that the products.reference is also 'FLA_' and that the creations.name contains ' - Premium(' (which I forgot). Thanks for your help :)
    – thomasb
    Mar 6 '15 at 14:58
  • For the transformation from sub-selects to sum and join, I'm trying to, but I get too many rows in the count(*) because a creation can be in multiple bundles, but I want to count the creations.
    – thomasb
    Mar 6 '15 at 15:02
  • 1
    Does count(distinct...) help?
    – Lennart
    Mar 6 '15 at 15:04
  • @cosmo0, for the first suggestion, you should also use group by date_part('year', c.created_at), date_part('month', c.created_at), p.reference. Using the column aliases (defined in select) in the group by is not good practise anyway (and not allowed by most DBMS). Mar 6 '15 at 15:04
  • @Lennart: I don't have a working query without sub-queries yet, but I'll try it. @ypercude: I grouped by date_part() in both the sub-query and the main query but no change.
    – thomasb
    Mar 6 '15 at 15:14
1

Thanks to @Lennart, with the usage of count(distinct c.id), I can successfully both count the creations and the sum of amount:

select
date_part('year', c.created_at) as yearcrea,
date_part('month', c.created_at) as monthcrea,
p.reference,
count (distinct c.id) as nb_crea,
(sum(o.amount_ati_cents) / 100) as ca_euros

from creations as c
inner join products as p on c.product_id = p.id
inner join order_items as oi on oi.creation_id = c.id
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')
and c.name like '% - Premium(%'
and p.reference like 'FLA_%'

group by yearcrea, monthcrea, reference
order by yearcrea desc, monthcrea desc
2
  • Cosmo0, you should mark your answer as a solution
    – Lennart
    Mar 6 '15 at 20:46
  • Yep, I had to wait 2 days. Now I can :)
    – thomasb
    Mar 8 '15 at 14:14

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.