Sql code returns unexpected results. How to correct it

I'm using Firebird 3. There are 'sellers' master table for my contragent sellers, detailed 'Doc' table for goods income documents and subdetail table 'paym' for payments.

Seller: Seller_id, seller
Docs: doc_id, doc_summa, part_id
Paym: paid_id, doc_id, paid

Seller:

seller_id	seller
8	Firm1
45	Firm2

Docs:

doc_id	doc_summa	seller_id
346	1000	8
347	600	45
348	800	45

Paym:

paym_id	doc_id	paid
1	346	100
2	346	100
3	347	200
4	348	100
5	348	50

My aim is to get my residual debts(income-payment) like this:

seller_id	summa	paid	debt
8	1000	200	800
45	1400	350	1050

but get this multiplied, wrong summas:

seller_id	summa	paid	debt
8	2000	200	800
45	2200	350	1850

SELECT
 s.seller_id, 
 sum(d.doc_summa) as summa,
 sum(p.paid) as paid,
 sum(d.doc_summa)-sum(p.paid) as debt

FROM seller s Left Join Docs d  on s.seller_id=d.seller_id
             Left Join  paym p  on p.doc_id= d.doc_id

GROUP BY s.seller_id

What is wrong in my SQL code?

Solution

The problem with your query is that because there are multiple payments for doc_ids 346 and 348, you will have two rows with a doc_summa of 1000 and 800 respectively, when you then sum them, the total becomes 2000 for seller_id 8 and 2200 for seller_id 45.

To solve this, you need to consolidate (sum) the payments before joining with docs. For example like the following:

with consolidated_paym as (
  select 
    doc_id, 
    sum(paid) as total_paid
  from paym
  group by doc_id
)
select
  s.seller_id,
  sum(d.doc_summa) as summa,
  sum(p.total_paid) as paid,
  sum(d.doc_summa - p.total_paid) as debt
from seller s
left join docs d
  on d.seller_id = s.seller_id
left join consolidated_paym as p
  on p.doc_id = d.doc_id
group by s.seller_id

Fiddle: https://dbfiddle.uk/6_vyCu0w