Help with Select adding up accounts to pay and receive fields for MYSQL reporting

Question

I have two tables equal fields, ACCOUNTS TO PAY and ACCOUNTS TO RECEIVE, I need to generate a report taking the value Credit - Debit and leaving result, someone can help me, I’m picking for this, below the select I’m using and the link with print of the test result I have in the database.

https://www.cloudvine.com.br/exemplo.png

SELECT
tb_cr.tb_cr_credito AS tb_cr_credito,
tb_cr.tb_cr_id_fornecedor AS tb_cr_id_fornecedor,
tb_cr.tb_cr_dt_Lancamento AS tb_cr_dt_Lancamento,
tb_cr.tb_cr_vencimento AS tb_cr_vencimento,
tb_cr.tb_cr_descricao AS tb_cr_descricao,
tb_cr.tb_cr_valor AS Credito,
'' AS Debito,
tb_cr.tb_cr_situacao AS tb_cr_situacao
FROM tb_cr
WHERE tb_cr.tb_cr_situacao = 'Recebido'

UNION

SELECT
tb_cp.tb_cp_debito AS tb_cp_debito,
tb_cp.tb_cp_id_fornecedor AS tb_cp_id_fornecedor,
tb_cp.tb_cp_dt_Lancamento AS tb_cp_dt_Lancamento,
tb_cp.tb_cp_vencimento AS tb_cp_vencimento,
tb_cp.tb_cp_descricao AS tb_cp_descricao,
'' AS Credito,
tb_cp.tb_cp_valor AS tb_cp_valor,
tb_cp.tb_cp_situacao AS tb_cp_situacao
FROM tb_cp
where tb_cp.tb_cp_situacao = 'Pago' 

Answer 1

You can use the following strategy:

• Instead of using a String empty for the Credito and Debito, use 0, so you can use them as a calculation attribute;
• It covers all your query in a subquery;
• Use the clause SUM to obtain the total values of each column;
• Perform the operation based on this sum;

By doing this you’ll have something like:

SELECT (SELECT SUM(tb_cr.tb_cr_valor) AS Credito
          FROM tb_cr
         WHERE tb_cr.tb_cr_situacao = 'Recebido') -
       (SELECT SUM(tb_cr.tb_cr_valor) AS Credito
          FROM tb_cr
         WHERE tb_cr.tb_cr_situacao = 'Pago') AS saldo

See functionando on SQL Fiddle.

---

SUM([DISTINCT] expr) [over_clause]

Returns the sum of expr. If the Return set has no Rows, SUM() Returns NULL.

In free translation:

Returns to some of a expressao. If the returned set does not contain lines, SUM() will return NULL.