Sum and total sql

Question

Hello I have the following sql: `SELECT sum(value) the exits, (select sum(value) FROM entries WHERE id_type = 1 ) recipes

FROM lancamentos

WHERE id_type = 2

group by id_tipo

`

It’s working ok, I need it to bring me the difference between recipes - exits, in a total column.

being like this:

Inflows | Outflows| Total 1.000 | -450 | 550,00

Answer 1

Based on your logic, I think it solves:

SELECT sum(valor) as saidas, 
(select sum(valor) FROM lancamentos WHERE id_tipo = 1 ) as receitas,
( ( sum(valor) as saidassoma ) - ( (select sum(valor) FROM lancamentos WHERE id_tipo = 1 ) as receitassoma ) as resultado
FROM lancamentos
WHERE id_tipo = 2
group by id_tipo

But I think with WHEN it would be clearer.

SELECT 
  SUM(CASE WHEN id_tipo = 1 THEN VALOR ELSE 0 END) AS RECEITAS,
  SUM(CASE WHEN id_tipo = 2 THEN VALOR ELSE 0 END) AS DESPESAS,
  SUM(CASE WHEN id_tipo = 1 THEN VALOR ELSE 0 END) - 
     SUM(CASE WHEN id_tipo = 2 THEN VALOR ELSE 0 END) AS RESULTADO
FROM LANCAMENTOS
GROUP BY id_tipo

Answer 2

Follow a practical example

Example

create table wp_grade(
id int primary key,
saidas decimal,
entradas decimal);

insert into wp_grade values(1, 30, 200);
insert into wp_grade values(2, 230, 150);

select 
sum(saidas) as saidas,
sum(entradas) as entradas,
(sum(entradas) - sum(saidas)) as EntradasMenosSaidas
from wp_grade