Postgres - adding an aggregate function from another table

Question

Okay so I start with this query that works well:

SELECT org.organization_id, org.comp_code, org.name, cust.name as customer,
o.order_number as number, o.order_id as id, cast(o.total_charge as real) as receivable 
    FROM 
    organization as org, orders as o, organization as cust, 
    reconcile_order as ro
    WHERE org.organization_id = o.shipper_org_id
    and o.broker_org_id = cust.organization_id
    and o.order_id = ro.order_id
    and o.status = 'D'
    and org.comp_code = 'ABC'
    and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)

But now I want add a sum from another table for each order_id. This other table is called reconcile_receivables and it has a column called amount. But the trick is that multiple amounts can relate to a unique order_id. So, I need to sum these amounts for every unique order. This is what I tried:

SELECT org.organization_id, org.comp_code, org.name, cust.name as customer,
o.order_number as number, o.order_id as id, cast(o.total_charge as real) as receivable, 
sum(rr.amount) as partial_pay 
    FROM
    organization as org, orders as o, organization as cust,
    reconcile_order as ro, reconcile_receivables as rr
    WHERE org.organization_id = o.shipper_org_id
    and o.broker_org_id = cust.organization_id
    and o.order_id = ro.order_id
    and o.order_id = rr.order_id
    and o.status = 'D'
    and org.comp_code = 'ABC'
    and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)

My problem is I get this error:

column "org.organization_id" must appear in the GROUP BY clause
or be used in an aggregate function

Adding a group by clause doesn't help. So how can I solve this to show a column that represents the sum of every rr.amount related to each o.order_id? For example, there are two rr.amount values whose sum is 1000 and they relate to order_id = 3. So for that row, it should show sum amount column with the value of 1000

Add the non aggregate columns in Group by

Pரதீப்
– Pரதீப்

2016-10-25 15:24:52 +00:00
Commented Oct 25, 2016 at 15:24 — Pரதீப்
– Pரதீப், Commented Oct 25, 2016 at 15:24

Laurenz Albe · Accepted Answer · 2016-10-25 15:28:54Z

1

You should do what PostgreSQL recommends and add a GROUP BY clause.

You will have to add all columns from the output list that are not in an aggregate function like sum() or count().

As an aside, is it possible that there are orders for which there is no reconcile_receivables? In that case you should use a LEFT OUTER JOIN.

answered Oct 25, 2016 at 15:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Robert Over a year ago

Your aside is correct, so I did left outer join rr on o.order_id = rr.order_id and then added all the other conditions below that. However, now I get a relation rr does not exist error which is strange

Kamil Gosciminski Over a year ago

rr doesn't exist. left outer join reconcile_receivables rr on ...

Robert Over a year ago

I tried that too but now I get table name "rr" specified more than once so then when I remove my earlier specification of rr I get invalid reference to FROM-clause entry for table "o" I am stuck in a loop of errors.

Laurenz Albe Over a year ago

It seems that you are not familiar with ANSI join syntax. Instead of

FROM organization as org, orders as o, organization as cust, reconcile_order as ro, reconcile_receivables as rr WHERE o.order_id = rr.order_id

use

FROM organization as org, organization as cust, reconcile_order as ro, orders as o LEFT OUTER JOIN reconcile_receivables as rr ON o.order_id = rr.order_id

.

antonio_antuan · Accepted Answer · 2016-10-25 18:20:13Z

You can use window function, replace problem column with this:

sum(rr.amount) over(partition by order.id) as partial_pay

I think it can help

UPD I see entire query text and I found, that you can use such query:

SELECT org.organization_id,..., sum(rr.amount) as partial_pay FROM organization as org, orders as o, organization as cust, reconcile_order as ro, (select sum(amount) as amount, order_id FROM reconcile_receivables GROUP BY order_id) as rr WHERE org.organization_id = o.shipper_org_id and o.broker_org_id = cust.organization_id and o.order_id = ro.order_id and o.order_id = rr.order_id and o.status = 'D' and org.comp_code = 'ABC' and (ro.receive_payment_in_full = 0 or ro.receive_payment_in_full is NULL)

It can be more optimized and much faster query, than the first, which uses window function. It depends on your database structure (e.g. indexes using) and numbers of rows

Collectives™ on Stack Overflow

Postgres - adding an aggregate function from another table

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related