Sum the total value of two columns, subtract, and return even if negative Mysql

Question

I have this following query:

SELECT FORMAT(SUM(receitas_dia) - SUM(despesas_dia),2,'de_DE') AS saldo_efetivo
FROM
  (SELECT SUM(r.valor_receita) AS receitas_dia,

     (SELECT SUM(d.valor_despesa)
      FROM despesas AS d
      WHERE d.pago = 1 AND d.data_vencimento = r.data_vencimento
        AND d.id_usuario = r.id_usuario) AS despesas_dia
   FROM receitas AS r
   WHERE r.recebido = 1 AND YEAR(r.data_vencimento) <= '2017'
     AND MONTH(r.data_vencimento) <= '06'
     AND r.id_usuario = 1
   GROUP BY r.data_vencimento) AS receita_despesas

It works well if the value of the revenue is greater than that of the expense... But if the value of the revenue is lower, it does not return the negative value, and yes NULL. How do I so that even if the recipe is smaller, it subtracts and returns the negative real value. (Fields of type DECIMAL).

@EDIT: My final query:

SELECT 
 FORMAT(SUM(t.valor),2,'de_DE') AS saldo_efetivo
FROM (
 SELECT
    d.valor_despesa*-1 AS valor,
    d.data_vencimento,
    d.pago AS realizado,
    d.id_usuario
 FROM despesas AS d

 UNION

 SELECT
   r.valor_receita,
   r.data_vencimento,
   r.recebido AS realizado,
   r.id_usuario
 FROM receitas AS r
) t WHERE YEAR(t.data_vencimento) <= 2017
     AND MONTH(t.data_vencimento) <= 06
     AND t.realizado = 1
     AND t.id_usuario = 1

Answer 1

What actually happens is, when there’s a day when there was no expense, or there was no revenue, the column is returned as null, when performing an operation with the column null the result will be null.

You can use the coalesce or ifnull to treat these conditions:

SELECT FORMAT(COALESCE(SUM(receitas_dia),0) - COALESCE(SUM(despesas_dia),0),2,'de_DE') AS saldo_previsto
FROM
  (SELECT SUM(coalesce(r.valor_receita,0)) AS receitas_dia,

 (SELECT SUM(coalesce(d.valor_despesa,0))
  FROM despesas AS d
  WHERE d.pago = 1 AND d.data_vencimento = r.data_vencimento
    AND d.id_usuario = r.id_usuario) AS despesas_dia


 FROM receitas AS r
   WHERE r.recebido = 1 AND YEAR(r.data_vencimento) <= '2017'
     AND MONTH(r.data_vencimento) <= '06'
     AND r.id_usuario = 1
   GROUP BY r.data_vencimento) AS receita_despesas

Edit:

Well, looking at your code and your need, I realize that the problem isn’t exactly that a column comes null, but rather, that you only bring the amount of expense to the day you had revenue, using a subselect of expenses, within a subselect of revenues. In addition to giving a GROUP BY in a column that is not displayed in the query.

Believing that your need is to have the date and balance for this date, I changed your query. See if it meets:

SELECT 
 SUM(t.valor)
From (
 Select 
    d.valor_despesa*-1 as valor,
    d.data_vencimento,
d.pago as recebido,
d.id_usuario
 FROM despesas d


 UNION

 SELECT
   r.valor_receita,
   r.data_vencimento,
   r.recebido,
   r.id_usuario
 FROM receitas 
) t WHERE t.recebido = 1 AND YEAR(t.data_vencimento) <= 2017
     AND MONTH(t.data_vencimento) <= 06
     AND WHERE t.recebido = 1 AND YEAR(t.data_vencimento) <= 2017
     AND MONTH(t.data_vencimento) <= 06
     AND t.id_usuario = 1

But attention, using Where this way, the database will load all the data from the two tables and then filter. In few records, no problems, in many, can be a major performance problem

Answer 2

The problem is that if you don’t have revenue the value returned will be NULL. Use the function IFNULL to get around:

SELECT IFNULL(FORMAT(SUM(receitas_dia), 0) - IFNULL(SUM(IFNULL(despesas_dia, 0)),2,'de_DE'), 0) AS saldo_previsto
FROM
  (SELECT SUM(IFNULL(r.valor_receita, 0)) AS receitas_dia,

     (SELECT SUM(IFNULL(d.valor_despesa, 0))
      FROM despesas AS d
      WHERE d.pago = 1 AND d.data_vencimento = r.data_vencimento
        AND d.id_usuario = r.id_usuario) AS despesas_dia
   FROM receitas AS r
   WHERE r.recebido = 1 AND YEAR(r.data_vencimento) <= '2017'
     AND MONTH(r.data_vencimento) <= '06'
     AND r.id_usuario = 1
   GROUP BY r.data_vencimento) AS receita_despesas