What's wrong with this query? [closed]

Question

What's wrong with this query? [closed]

Navigation

#1 by (1 votes)

1

I want to do a subtraction of the total value of a column of a table, with the total value of a column of 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

The where clause of the 'expenditure' table should be the same as the where of revenue clause.

EDIT: Tables: link

So it 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'm passing the year, month, and user id parameters again. Is there any way to make this work without having to pass the values twice?

mysql

asked by anonymous 19.06.2017 / 18:12

1 answer

Pattern Background Color www htaccess redirect with segment in url

score 1 · Accepted Answer

Try doing 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 is to align the sum of expenses and revenues by date. After that, the sum of the columns is done and then the subtraction is done.

Note: I did not test the query in MySQL, only in SQLServer. You may need to adapt something for MySQL.