subtraction of values between columns other than the same table

Question

is the following, I am trying to get the result of a subtraction through a query between different columns of the same table.

I have the following table.

As you can see in the table, I have the fact_type column, which can be (expense or revenue) and for each type is inserted a value that is stored in the value column, until then everything is right. However, after inserting the values in the table, I need to obtain the result of the subtraction between the values of tip_lancamento = revenue minus values of tip_lancamento = expense.

I’m trying this way, but it’s making a mistake

SELECT SUM(value) WHERE type_lancing = 'revenue' - SUM(value) type_lancing = 'expense' FROM lancings;

Answer 1

I would trade the tipo_lancamento by an int type to gain more performance and be more precise. Failed to put the table name elsewhere too but the rest I would do so:

in Firebird:

Select (
    (SELECT SUM(valor) from lancamentos WHERE tipo_lancamento = 'receita') 
   - (SELECT SUM(valor) from lancamentos WHERE tipo_lancamento = 'despesa')
)from rdb$database