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:

Docs:

Paym:

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

but get this multiplied, wrong summas:

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?

CodePudding user response：

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