DOUBT ABOUT SUBSELECT IN FIREBIRD

Question

Hello, good evening! I have a table that involves the input and output part of a box, an example of this is: no day 01/01/2018 the user reported that entered R $ 200,00 in the company’s vault, the same day he performed another entry operation in the amount of R $ 800,00. At the end of the day he withdrew R $ 500,00 totaling the balance of the day in the amount of R $ 500,00 only related to operations.

In my database it’s described like this

Data       Op           Valor
01/01/2018 +            200
01/01/2018 +            800
01/01/2018 -            500

It is the same way it is just above, I would like to try to get the input values minus the output values, I created this select:

SELECT
SUM(SELECT A.DINHEIRO 
FROM TVENSANGRIASUPRIMENTO A 
WHERE A.OPERACAO = '+' 
AND A.IDSANGRIASUPRIMENTO = B.IDSANGRIASUPRIMENTO)

-

SUM(SELECT A.DINHEIRO 
FROM TVENSANGRIASUPRIMENTO A 
WHERE A.OPERACAO = '-' 
AND A.IDSANGRIASUPRIMENTO = B.IDSANGRIASUPRIMENTO)
FROM TVENSANGRIASUPRIMENTO B

But none of this is working, anyone could help me?

Sincerely yours.

Answer 1

You don’t need a Subselect for that, just use the CASE

SELECT 
    CASE WHEN OPERACAO = '+' THEN SUM(DINHEIRO) END SaldoEntrada,
    CASE WHEN OPERACAO = '-' THEN SUM(DINHEIRO) END SaldoSaida, 
    (CASE WHEN OPERACAO = '+' THEN SUM(DINHEIRO) END) - (CASE WHEN OPERACAO = '-' THEN SUM(DINHEIRO) END) AS SaldoDiferenca

FROM TVENSANGRIASUPRIMENTO