Add the total value of two columns, subtract, and return even if it is negative MySQL

Question

Add the total value of two columns, subtract, and return even if it is negative MySQL

Navigation

#1 by (3 votes)
#2 by (2 votes)

1

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 smaller, it does not return the negative value, but NULL . How do I make even though the recipe is smaller, it subtracts and returns the negative real value. (DECIMAL type fields).

@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

mysql

asked by anonymous 24.06.2017 / 15:34

2 answers

2

The problem is that if you have no revenue the value returned will be NULL . Use the IFNULL function to work 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

24.06.2017 / 16:20

Error Page 404 STANDARD PHP HTACCES How to validate a wrong digit data?

score 3 · Accepted Answer

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

You can use 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, analyzing your code and your need, I realize that the problem is not exactly the fact that a column comes null, but rather, that you only bring the expense amount to the day you earned it, using a subselect of expenses, within a subselect of revenues. In addition to giving a GROUP BY in a column that does not appear in the query.

Believing that your need is to have the date and balance for this date, I've changed your query. See if you can:

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 pay attention, using the where in this way, the database will load all the data from the two tables and then filter. In a few records, no problems in many, can be a big performance problem