What’s wrong with this query?

Asked

Viewed 88 times

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?

  • 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.

1 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.

  • In my first test it worked perfect! I need to test some cases to see if it’s really ok... for now it’s working fine! Thanks! Anything I return here to speak...

Browser other questions tagged

You are not signed in. Login or sign up in order to post.