Query with LEFT OUTER JOIN on subqueries with SUMs

Question

Im trying to perform an OUTER JOIN on related tables, but I want to JOIN the SUMs not the actual data. This query is flawed so I am looking for help on this structure, but also Im curious if there is a more elegant way of performing this type of query.

SELECT firstname,lastname, thesum1, thesum2 FROM whovians 
LEFT OUTER JOIN (
        SELECT SUM(thevalue) AS thesum1 FROM friends WHERE doctornumref = 10 AND year = 1968
    ) AS derivedTable1 
ON (whovians.doctornum = friends.doctornumref) 
LEFT OUTER JOIN (
        SELECT SUM(amount) AS thesum2 FROM enemies WHERE doctornumref = 10 AND year = 1968
    ) AS derivedTable2 
ON (whovians.doctornum = enemies.doctornumref) WHERE year = 1968 AND doctornum = 10;

Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect, justified by reference to authoritative documentation, or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] — philipxy, Sep 03 '22 at 01:53

Erwin Brandstetter · Accepted Answer · 2022-09-03T01:48:45.957

Should work like this:

SELECT w.firstname, w.lastname, derived1.thesum1, derived2.thesum2
FROM   whovians w
LEFT   JOIN (
   SELECT doctornumref, SUM(thevalue) AS thesum1
   FROM   friends 
   WHERE  doctornumref = 10 
   AND    year = 1968
   GROUP  BY 1
   ) AS derived1 ON derived1.doctornumref = w.doctornum
LEFT   JOIN (
   SELECT doctornumref, SUM(amount) AS thesum2 
   FROM   enemies 
   WHERE  doctornumref = 10 
   AND    year = 1968
   GROUP  BY 1
   ) AS derived2 ON derived2.doctornumref = w.doctornum 
WHERE  w.doctornum = 10
AND    w.year = 1968;

In this particular case, since you restrict to the same year and doctornumref / doctornum in outer query as well as subqueries, and the subquery can only return 0 or 1 rows, you can simplify with lowly correlated subqueries:

SELECT firstname,lastname
     , (SELECT SUM(thevalue)
        FROM   friends 
        WHERE  doctornumref = w.doctornum
        AND    year = w.year) AS thesum1
     , (SELECT SUM(amount)
        FROM   enemies 
        WHERE  doctornumref = w.doctornum
        AND    year = w.year) AS thesum2
FROM   whovians w
WHERE  year = 1968
AND    doctornum = 10;

If (year, doctornum) is not unique in table whovians, the first form will prevent repeated evaluation of the subqueries and perform better, though.

You can still simplify:

SELECT w.firstname, w.lastname, f.thesum1, e.thesum2
FROM   whovians w
LEFT   JOIN (
   SELECT SUM(thevalue) AS thesum1
   FROM   friends 
   WHERE  doctornumref = 10 
   AND    year = 1968
   ) f ON true  -- 0 or 1 row in subquery, guaranteed to match
LEFT   JOIN (
   SELECT SUM(amount) AS thesum2 
   FROM   enemies 
   WHERE  doctornumref = 10 
   AND    year = 1968
   ) e ON true
WHERE  w.doctornum = 10
AND    w.year = 1968;

@roberthuttinger: The moment you attach an alias to a source table, only this alias is visible, not the original name. You can join in the same table multiple times this way, but here it's just to shorten the code. More about column and table aliases: http://stackoverflow.com/questions/20229979/query-to-order-by-the-number-of-rows-returned-from-another-select/20230716#20230716 — Erwin Brandstetter, Aug 20 '14 at 18:10

Query with LEFT OUTER JOIN on subqueries with SUMs

1 Answers1