Simplify subtraction between MySQL tables

Question

Simplify subtraction between MySQL tables

Navigation

#1 by (0 votes)

0

I wanted to know how to simplify the following excerpt:

SELECT FORMAT((SUM(r.valor_receita) - (SELECT SUM(d.valor_despesa)
FROM despesas AS d WHERE d.pago = 1 AND YEAR(r.data_vencimento) <= YEAR(d.data_vencimento)
AND MONTH(r.data_vencimento) <= MONTH(d.data_vencimento) AND r.id_usuario = d.id_usuario)),2,'de_DE')
AS saldo_efetivo
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

What it currently does is add up the value of all incoming receipts (received = 1) and subtract with the subselect that sums up all paid expenses (pay = 1). I would like to know if you can simplify this query.

php mysql

asked by anonymous 19.06.2017 / 02:46

1 answer

Create trigger in oracle Inner Join Database

score 0 · Answer 1

As the table structure is set up I believe there is no better way to do it, but if the table structures are the same, I advise you to leave everything in the same table with a field that defines the type ("Expense" or "Revenue"), for example:

SELECT
    SUM(IF(tipo = 1,valor,valor*-1)) -- 1 - Receita / 2 - Despesa, Sendo assim faria o valor *-1 para ficar negativo
FROM tabela_contas
WHERE confirmado = 1 -- Seria se o valor foi pago ou recebido
-- E aqui o resto das suas condições