Re: Avoiding double-counting in aggregates with more than one join? - Mailing list pgsql-general

From David G. Johnston
Subject Re: Avoiding double-counting in aggregates with more than one join?
Date
Msg-id CAKFQuwb2o5adN0dartbg-_1e6DY2yC7oy9h3EvyEyDa4=7OLGw@mail.gmail.com
Whole thread Raw
In response to Avoiding double-counting in aggregates with more than one join?  (Paul Jungwirth <pj@illuminatedcomputing.com>)
List pgsql-general
On Fri, Nov 18, 2016 at 10:16 AM, Paul Jungwirth <pj@illuminatedcomputing.com> wrote:
But is there a better way?

​Nothing that would be more readable nor likely more performant.

When performing aggregation it is necessary to limit the scope of the query to only whatever it is you are calculating.  Since you wish to compute two things you need two separate parts ​plus a third to combine them.

​If performance is a concern you should move the aggregation queries directly to the main query instead of using the optimization fencing CTE.

SELECT
FROM products
LEFT JOIN (
SELECT sum()
)​ s USING (product_id)
LEFT JOIN (
SELECT sum()
) r USING (product_id)

​If the second "scope" doesn't need to be calculated but simply informs the one-and-only aggregate you should use SEMI JOIN (EXISTS) instead of a INNER/LEFT JOIN​.  But that isn't what you have here.

David J.

pgsql-general by date:

Previous
From: Paul Jungwirth
Date:
Subject: Avoiding double-counting in aggregates with more than one join?
Next
From: Andreas Terrius
Date:
Subject: Partial update on an postgres upsert violates constraint