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

​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.