Re: Avoiding double-counting in aggregates with more than one join?

On Fri, Nov 18, 2016 at 10:16 AM, Paul Jungwirth <
pj(at)illuminatedcomputing(dot)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.