How to correctly calculate the difference between the sums in the query SELECT (a.sum) a_sum, (b.sum) b_sum FROM a INNER JOIN b ON a.id=b.id?

Question

SQL

there are two tables
in one id-nomenclature-weena-quantity-amount
in another id_first_table - payment
I make a query
SELECT a.sum a_sum, b.sum b_sum, ... FROM a INNER JOIN b ON a.id=b.id I output
by the lines are beautiful, everything is as it should be at the end of
the table , I make a
SELECT (a.sum) a_sum, (b.sum) b_sum FROM a INNER JOIN b ON a.id=b.id query
to display the totals.
And here a problem arises when buying several items with one check - payment data is additionally summarized in the number of positions in the check,
for example
, 01/01/2000 apple 1 piece 10r = 10r, payment 10r
01/02/2000 pear 2 pcs 20r = 40r, payment 40r
01/03/2000 apple 1 piece 10r = 10r, payment 50r
01/03/2000 pear 2 pcs 20r = 40r, payment 50r
last lines in payments are counted twice

Answer 1

Standardize Накладная, позиции накладнойand оплата накладной. The amounts will not be duplicated.