0
I have the following query:
SELECT FORMAT(valor,2,'de_DE') AS valor,
DATE_FORMAT(data_vencimento, '%d/%m/%Y') AS data_vencimento,
realizado,
DATE_FORMAT(data_realizado, '%d/%m/%Y') AS data_realizado,
parcelas,
numero_parcela,
observacoes,
lancamento,
tipo_movimento,
tipo,
id_usuario
FROM
(SELECT r.valor_receita AS valor,
r.data_vencimento AS data_vencimento,
r.recebido AS realizado,
r.data_recebimento AS data_realizado,
r.parcelas AS parcelas,
r.numero_parcela AS numero_parcela,
r.observacoes AS observacoes,
'Receita' AS lancamento,
re.tipo_recebimento AS tipo_movimento,
tr.tipo_receita AS tipo,
r.id_usuario AS id_usuario
FROM receitas AS r
INNER JOIN tipo_receita AS tr ON (r.id_tipo_receita = tr.id_tipo_receita)
INNER JOIN tipo_recebimento AS re ON (r.id_tipo_recebimento = re.id_tipo_recebimento)
UNION SELECT d.valor_despesa AS valor,
d.data_vencimento AS data_vencimento,
d.pago AS realizado,
d.data_pagamento AS data_realizado,
d.parcelas AS parcelas,
d.numero_parcela AS numero_parcela,
d.observacoes AS observacoes,
c.nome_categoria AS lancamento,
tp.tipo_pagamento AS tipo_movimento,
td.tipo_despesa AS tipo,
d.id_usuario AS id_usuario
FROM despesas AS d
INNER JOIN tipo_despesa AS td ON (d.id_tipo_despesa = td.id_tipo_despesa)
INNER JOIN tipo_pagamento AS tp ON (d.id_tipo_pagamento = tp.id_tipo_pagamento)
INNER JOIN categorias AS c ON (td.id_categoria = c.id_categoria)) AS origem
WHERE id_usuario = 1
I want to know how to add two columns, one that adds the valor_receita
(total) of the revenue table, and the other valor_despesa
(total) of the expenditure schedule.
Tables: https://pastebin.com/rexcXTKw
It would be more or less that, however, I’m passing some filters in the PHP code to increment the query. There has to be the total value based on this filter tbm.
– Eduardo Henrique
SUM(r.value_revenue) AS total_revenue, ai will add only the values of the records found
– HENRIQUE LOBO
This way only returns one record.. and not all
– Eduardo Henrique
Gives a GROUP BY id_revenue, id_expenses, you can simplify and make two queries, one to know the value of revenues and the other of expenses.
– HENRIQUE LOBO