What’s wrong with this query?

Question

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?

Answer 1

Try to do something like this:

select sum(receitas_dia) - sum(despesas_dia)
from (
    select
        SUM(r.valor_receita) as receitas_dia,
        (
            select SUM(d.valor_despesa) 
            from despesas as d 
            where d.data_vencimento = r.data_vencimento
              and d.id_usuario = r.id_usuario) as despesas_dia
    from 
        receitas as r 
    where 
        year(r.data_vencimento) = '2017' 
        and month(r.data_vencimento) <= '06'
    group by r.data_vencimento) as receita_despesas;

In this case, two queries are made. The first serves to align sum of expenses and revenues by date. After this, the columns are summed and then subtracted.

Note: I have not tested the query in Mysql, only in Sqlserver. Maybe I need to adapt something to Mysql.