CODEWITHSUNDEEP
postgresqljoinsubquery
ghukill
ghukill

Reputation: 1202

postgres sum different table columns from many to one joined data

Suppose I have the following two tables:

foo:

| id | goober | value |
|----|--------|-------|
| 1  | a1     | 25    |
| 2  | a1     | 125   |
| 3  | b2     | 500   |

bar:

| id | foo_id | value |
|----|--------|-------|
| 1  | 1      |  4    |
| 2  | 3      |  19   |
| 3  | 3      |  42   |
| 4  | 3      |  22   |
| 5  | 3      |  56   |

Note the n:1 relationship of bar.foo_id : foo.id.

My goal is to sum the value columns for tables foo and bar, joining on bar.foo_id=foo.id, and finally grouping by goober from foo. Then performing a calculation if possible, though not critical.

Resulting in a final output looking something like:

| goober | foo_value_sum | bar_value_sum | foo_bar_diff |
|--------|---------------|---------------|--------------|
| a1     | 150           | 4             | 146          |
| b2     | 500           | 139           | 361          |

Upvotes: 2

Views: 444

Answers (1)

Philipp Johannis
Philipp Johannis

Reputation: 2936

This should be rather simple by the following query that creates two CTEs and then joins them afterwards:

with bar_agg as
(
    select foo.goober
    ,sum(bar.value) bar_value_sum
    from foo
    join bar
      on bar.foo_id = foo.id
    group by foo.goober
)
,foo_agg as 
(
    select foo.goober
    ,sum(foo.value) foo_value_sum
    from foo
    group by foo.goober
)
select foo.goober
,foo_value_sum
,bar_value_sum
,foo_value_sum - bar_value_sum foo_bar_diff
from foo_agg foo
left join bar_agg bar
  on bar.goober = foo.goober
order by foo.goober

Upvotes: 2

Related Questions