1
I am wanting to subtract the total value of a column from a table, with the total value of a column from another table.
Query:
SELECT (
SUM(r.valor_receita) -
(
SELECT SUM(d.valor_despesa)
FROM
despesas AS d
WHERE
YEAR(d.data_vencimento) = YEAR(r.data_vencimento)
AND MONTH(d.data_vencimento) <= MONTH(r.data_vencimento)
AND d.id_usuario = r.id_usuario)
) AS saldo_previsto
FROM
receitas AS r
WHERE
YEAR(r.data_vencimento) = '2017'
AND MONTH(r.data_vencimento) <= '06'
AND r.id_usuario = 1
In the clause where
of the 'expenses' table shall be the same value as the clause where
revenue.
EDIT: Tables: https://pastebin.com/rexcXTKw
This should be the query:
SELECT (
SUM(r.valor_receita) -
(
SELECT SUM(d.valor_despesa)
FROM
despesas AS d
WHERE
YEAR(d.data_vencimento) = '2017'
AND MONTH(d.data_vencimento) <= '06'
AND d.id_usuario = 1)
) AS saldo_previsto
FROM
receitas AS r
WHERE
YEAR(r.data_vencimento) = '2017'
AND MONTH(r.data_vencimento) <= '06'
AND r.id_usuario = 1
The only difference is that I am passing again the parameters of year, month, and user id. Is there any way to make it work without having to pass the values twice?
What a mistake you’re making?
– Diego
You’re not returning error... In fact what happens is that it does not return the value that should return... It should sum all revenues (revenue table) and subtract for all expenses (expense table), when the year is equal to the selected year and the month less or equal to the selected month.. this being the condition for the two tables.
– Eduardo Henrique