CODEWITHSUNDEEP
sqlpostgresqlsubqueryleft-joinaggregate-functions
Eno
Eno

Reputation: 10850

Select rows in left join which depend on sum of a field in the other table?

Im trying to write a SQL left outer join query where the left rows are selected based on the sum of a field in rows in the other (right) table. The other table has an id field that links back to the left table and there is a one-to-many relationship between left and right tables. The tables (simplified to relevant fields only) look like this:

left_table:

+--------+
| id     |
| amount |
+--------+

right_table:

+-------------------+
| id                |
| amount            |
| left_table_row_id |
+-------------------+

Basically the right table's rows' amount fields have fractions of the amounts in the left table and are associated back to the left_table, so several right_table rows might be linked to a single left_table row.

Im trying to select only left_table rows where left_table.id=right_table_id and where the sum of the amounts in the right_table's rows with linked id are equal to left_table.amount. We can't use aggregate in a WHERE clause and I had no luck with using HAVING. I hope that makes sense.

Upvotes: 0

Views: 367

Answers (2)

GMB
GMB

Reputation: 222462

You can filter with a correlated subquery:

select l.*
from left_table l
where l.amount = (select sum(r.amount) from right_table r where r.id = l.id)

Upvotes: 1

Philipp Johannis
Philipp Johannis

Reputation: 2936

This should be possible with the following query:

with agg as
(
    select left_table_row_id,sum(amount) as amount
    from right_table
    group by left_table_row_id
)
select *
from left_table lt
where exists (select 1 from agg where lt.id=agg.left_table_row_id and lt.amount = agg.amount)

Upvotes: 0

Related Questions