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;

Answer 1

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;