Get sum of a column in a join: derived table

In the example below I wish to return a row for each row in b and return the
sum of a.val where b.id = a.id. At the bottom is a working select but I don't
know how it works! I learnt the technique from a friend but it is a
mystery to me and I don't know if there are any better techniques for
achieving my goal.

I have read in the Manual (section 4.2.1.x) references to "derived
tables". Clearly a2 in my example is derived. What are the rules for
derivation; can they come only after a FROM? The section in the manual
simply states this as and "interesting case!".

How do you go about optimising this sort of query?

----------------------------------------------------------------------------
4.2.1.3. Subqueries

Subqueries specifying a derived table must be enclosed in
parentheses and must be assigned a table alias name. (See Section
4.2.1.2.) For example:

FROM (SELECT * FROM table1) AS alias_name

This example is equivalent to FROM table1 AS alias_name. More
interesting cases, which can't be reduced to a plain join, arise
when the subquery involves grouping or aggregation.

----------------------------------------------------------------------------

dd=# select * from b; dd=# select * from a;
id | val id | val
----+----- ----+-----
2 | 1 1 | 2
4 | 1 2 | 4
(2 rows) 3 | 3
2 | 5
(4 rows)

----------------------------------------------------------------------------

dd=#
SELECT
b.id as bid, b.val as bval, a2.id, sum as aval
FROM b
LEFT OUTER JOIN
(select id, sum(val) from a group by id) as a2
ON b.id = a2.id;

bid | bval | id | aval
-----+------+----+------
2 | 1 | 2 | 9
4 | 1 | |
(2 rows)

--
Rory Campbell-Lange
<rory(at)campbell-lange(dot)net>
<www.campbell-lange.net>