postgresql column sums across a join, grouped by another column

Question

Suppose I have the following PostgreSQL tables:

table: foo:

foo_id, baz, amount
--------------------
f1, b1, 500
f2, b1, 500
f3, b2, 1000
f4, b3, 100

table: bar:

bar_id, foo_id, amount
--------------------
br1, f1, 10
br2, f1, 10
br3, f1, 10
br4, f2, 10
br5, f2, 10
br6, f3, 750
br7, f3, 250
br8, f4, 90
br9, f4, 9

My goal is to sum up foo.amount and bar.amount, and find the difference, grouped by foo.baz.

Example results:

baz, sum_foo_amount, sum_bar_amount, diff_foo_bar
-------------------------------------------------
b1, 1000, 50, 950
b2, 1000, 1000, 0
b3, 100, 99, 1

I managed to get the expected results using a couple CTE's -- one summing up foo, and one summing up bar -- then selecting from foo and grouping. But I'm actually dealing with ~1m rows for bar and it was not performant to say the least.

I feel confident I could cobble something together eventually, but I'm curious for insights on patterns or approaches I may not see.

Answer 1

Not one of my proudest questions or answers, but posting the currently in-use solution for posterity:

select    
    baz,
    foo_sum,
    bar_sum,
    (foo_sum - bar_sum) as difference
from    
    (
    with x as (
        select           
            foo.baz as baz,
            cast(foo.amount as decimal) as foo_sum,
            cast(sum(bar.amount) as decimal) as bar_sum
        from bar
        inner join foo on bar.foo_id = foo.foo_id        
        group by bar.foo_id, foo.baz, foo.amount
    )
select       
       x.baz as baz,
       sum(x.foo_sum) as foo_sum,
       sum(x.bar_sum) as bar_sum
from x
group by x.baz
    ) as foo_bar_calculations;

Producing the results:

 baz | foo_sum | bar_sum | difference 
-----+---------+---------+------------
 b2  |    1000 |    1000 |          0
 b3  |     100 |      99 |          1
 b1  |    1000 |      50 |        950
(3 rows)