Group cells case when empty sql

Question

Group cells case when empty sql

Navigation

#1 by (2 votes)

2

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

The parcel value is transaction 3 in the bank and the interest amount 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 as well

I would like to know if there is any way to group the plot amount and interest value columns on the same row.

I've tried to use group by but it did not work.

If anyone knows of a solution, thank you.

sql postgresql select case when

asked by anonymous 17.12.2018 / 19:45

1 answer

Loadstring, how to use? Polymorphism with ArrayList

score 2 · Accepted Answer

If I use 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 do not know how you tested before, but this way you get the sum of the two fields.