SUM of one table minus the SUM of another in the same field

Question

Hello, I am self-taught and already do some consultations in SQL but "stuck" in this.

I need a list where one of the fields should show the total of a column, minus the total of another that is in a second table.

Example:

Table A (Invoices)

+------+---------+------------+------------+
| id   | docnum  |   valor    | clienteid  |
+------+---------+------------+------------+
  1        25        150,00       23
  2        26        300,00       22
  3        27        450,25       23
  3        28        600,00       23

Table B (Receipts)

+------+---------+------------------+-----------------------+--------+
| id   | docnum  | numfac | valor   | valorpago| clienteid  | Tipo   |
+------+---------+------------------+-----------------------+--------+
   1       1         25     150,00      150,00       23        REC 
   2       2         27     450,25      100,00       23        REC
   3      25         45     -25,00      -25,00       23        FAL 
   4       3         27     450,25      100,00       23        REC
   5      88         78      55,25      -55,25       23        FAL

The Table To contains the Invoices issued

The Table B Contains the Paid Amounts

I need to get a listing of outstanding invoices by taking partial payments and ignoring those that are already paid.

The result should be as follows:

Cliente ValorDocumento ValorEmDebito
   22       300,00        300,00
   23      1000,25        800,25 

if you use this query:

SELECT f.docnum AS Doc,
       f.clienteid AS Cliente,
       f.valor AS ValorDocumento,
       f.valor - SUM(COALESCE(r.valorpago, 0)) As ValorEmDebito
  FROM Facturas f
  LEFT JOIN Recibos r
    ON r.numfac = f.docnum
WHERE   (r.tipodoc IS NULL or r.tipodoc='rec' ) and f.clientid=23 
 GROUP BY f.docnum,
          f.clienteid,
          f.valor
HAVING f.valor - SUM(COALESCE(r.valorpago, 0)) > 0;

filtering by the customer 23 only partially paid invoice appears

  Cliente ValorDocumento ValorEmDebito
     23      450,25        250,25

and wanted it to appear

 Cliente ValorDocumento ValorEmDebito
   23       1000,25         600,25

Answer 1

Use the clause HAVING to filter the results you want (With the remaining output greater than 0):

SELECT f.docnum AS Doc,
       f.clienteid AS Cliente,
       f.valor AS ValorDocumento,
       f.valor - SUM(COALESCE(r.valorpago, 0)) As ValorEmDebito
  FROM Facturas f
  LEFT JOIN Recibos r
    ON r.numfac = f.docnum
 GROUP BY f.docnum,
          f.clienteid,
          f.valor
HAVING f.valor - SUM(COALESCE(r.valorpago, 0)) > 0;

To query above will result in:

| Doc | Cliente | ValorDocumento | ValorEmDebito |
| --- | ------- | -------------- | ------------- |
| 26  | 22      | 300            | 300           |
| 27  | 23      | 450.25         | 250.25        |

• In the query the two tables are linked by means of a LEFT JOIN because there may be some document without any payment and we want to show even these cases;
• The COALESCE is used for cases where there is no receipt, ensuring that the debit calculation works properly considering 0 in payment;
• The clause HAVING is used to filter the records considering only those that do not yet have payment greater or equal to the value of the invoice;
• The clause HAVING is used instead of WHERE because it needs aggregation functions (in this case the SUM). That is, the restriction will only be applied after the records are grouped.

COALESCE

Evaluates the arguments in order and returns the current value of the first expression that is not evaluated as NULL initially. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because this is the first value that is not null.

---

HAVING

Specifies a search criteria for a group or aggregation. HAVING can be used only with instruction SELECT. HAVING is usually used with a clause GROUP BY. When GROUP BY is not used, there will be a single aggregate and implicit group.

---

SUM

Returns the sum of all values or only the values DISTINCT in the expression. SUM can be used exclusively with numeric columns. Null values are ignored.

You can check the result on DB Fiddle.

Answer 2

I need to get a listing of outstanding invoices by taking partial payments and ignoring those that are already paid.

The result should be as follows:
Customer Valordocumento Valoremdebito

-- código #1
with 
soma_Recibos as (
SELECT numfac, sum (valorpago) as acumPgto
  from Recibos
  where tipodoc = 'REC' 
        or tipodoc is null
  group by numfac
),
Facturas_em_aberto as (
SELECT A.docnum, A.clienteid, A.valor, 
       coalesce (B.acumpgto, 0) as acumpgto
  from Facturas as A
       left join soma_Recibos as B on B.numfac = A.docnum
  where B.numfac is null
        or B.acumPgto < A.valor
)
SELECT clienteid, 
       sum (valor) as ValorDocumento,
       sum (valor - acumpgto) as ValorEmDebito
  from Facturas_em_aberto
  group by clienteid;

The above code considers that the invoice numbering is customer independent, as deduced from the data sample in Table A.

the code was built using CTE - common table Expression -, which facilitates programming, understanding and maintenance. Details in article "Modular programming with table expressions (CTE)”.

Answer 3

The query below allows you to show the results you want:

SELECT      A.docnum                        AS doc
        ,   A.clienteid                     AS cliente
        ,   A.valor                         AS valordocumento
        ,   A.valor - ISNULL(B.valor, 0)    AS valoremdebito
FROM        tabelaA     A
LEFT JOIN   (
                SELECT      numfac
                        ,   clienteid
                        ,   SUM(valorpago) AS valorpago
                FROM        tabelaB
                GROUP BY    numfac
                        ,   clienteid
            )           B   ON  B.numfac    = A.numfac
                            AND B.clienteid = A.clienteid
WHERE       (A.valor - ISNULL(B.valor, 0)) > 0
ORDER BY    A.docnum