Group empty case when sql cells

Question

I am making a query in the database to know in which branch the customer paid a installment and what is the normal value and interest that were paid.

The value of the tranche is transaction 3 at the bank and the value of interest is transaction 59.

Follow the sql below:

select 
adm_fili.cd_filial,
fin_lcto.nr_titulo,
fin_lcto.sufixo as parcela,
(case when (fin_lcto.cd_operacao=3)
 then fin_lcto.vl_operacao end) as vl_baixas,
 (case when (fin_lcto.cd_operacao=59)
 then fin_lcto.vl_operacao end) as vl_juros,
fin_lcto.dt_vcto as Vencimento
from fin_lcto
join adm_fili on (fin_lcto.cd_filial=adm_fili.cd_filial)

where cd_operacao in(3,59)
and fin_lcto.dt_lancamento between '2018-11-01' and '2018-11-01'

order by nr_titulo

The way I’m doing the bank returns the result like this

I wonder if there is some way to group the columns of installment value and interest value in the same row.

I tried to use group by but it didn’t work.

If anyone knows of a solution thank you.

Answer 1

If you use the GROUP BY I think you get what you want:

SELECT      adm_fili.cd_filial
        ,   fin_lcto.nr_titulo
        ,   fin_lcto.sufixo                             AS parcela
        ,   SUM(CASE    WHEN fin_lcto.cd_operacao = 3
                        THEN fin_lcto.vl_operacao END)) AS vl_baixas
        ,   SUM(CASE    WHEN fin_lcto.cd_operacao = 59
                        THEN fin_lcto.vl_operacao END)  AS vl_juros
        ,   fin_lcto.dt_vcto                            AS Vencimento
FROM        fin_lcto
JOIN        adm_fili on fin_lcto.cd_filial = adm_fili.cd_filial
WHERE       cd_operacao in (3, 59)
        AND fin_lcto.dt_lancamento BETWEEN '2018-11-01' AND '2018-11-01'
GROUP BY    adm_fili.cd_filial
        ,   fin_lcto.nr_titulo
        ,   fin_lcto.sufixo
        ,   fin_lcto.dt_vcto
ORDER BY    nr_titulo

I don’t know how you tested before, but this way you get the sum of the two fields.