Query in SQL/Firebird grouping a sum by nature

Question

I’m trying to make a Query in the product table to return to me the following information below:

I’m trying to do it in a grouped way by totaling the values. There is a spine by name nature, that has the launch on credit represented by 'C' and debiting represented by 'D'. That at the end I would like it to be in a single column, as shown above.

The caluna value receives the lacquers of nature 'C' least 'D' and would like the result column to show whether the result is positive or negative.

I have to group by group and value

I have the Query below, but I could not evolve

SELECT
CC.CODIGO_PRODUTO AS PRODUTO,
GS.GRUPO AS GRUPO,
GS.CODIGO_GRUPO AS NOME_GRUPO,
CC.TIPO_NATUREZA AS NATUREZA,
CC.VALOR
FROM LANCAMENTOS CC
INNER JOIN GRUPO GS ON GS.CODIGO_GRUPO = CC.CODIGO_GRUPO

Which results in this information:

How I can evolve to query? Thanks for your help.

Answer 1

Use the CASE / WHEN construction in the aggregation function:

SELECT
CC.CODIGO_PRODUTO AS PRODUTO,
GS.GRUPO AS GRUPO,
GS.CODIGO_GRUPO AS NOME_GRUPO,
SUM( CASE CC.TIPO_NATUREZA
    WHEN "C" THEN CC.VALOR
    WHEN "D" THEN -1*CC.VALOR END) AS TOTAL
FROM LANCAMENTOS CC
INNER JOIN GRUPO GS ON GS.CODIGO_GRUPO = CC.CODIGO_GRUPO
GROUP BY PRODUTO, GRUPO, NOME_GRUPO;