Add multi-line values under condition

Question

Add multi-line values under condition

Navigation

#1 by (3 votes)
#2 by (0 votes)

3

Good afternoon gentlemen, I have a query that returns me some fields I need the sum of them, only that before a condition

 select adm.adm_ds_administradora as descricao,
        sum(car.car_vl_cartao) as valor,
        'Cartão TEF'        as cartao,
        cast ('' as varchar) as fun_ds_funcionario,
        cast ('' as varchar) as ntd_vl_acrescimo,
        cast ('' as varchar) as ntd_vl_desconto,
        cast ('' as varchar) as ntd_vl_pago,
        cast ('' as varchar) as ntd_vl_saldo,
        cast ('' as varchar) as agrupamento,
        rank() over(
 order by adm.adm_ds_administradora) as ordenacao,
          adm.adm_fl_tipo,
          *CASE WHEN adm.adm_fl_tipo = 'D' then
            SUM(car.car_vl_cartao)
          END as total,*
          1 as CONSTANTE
 from financeiro.cartao car,
      cadastro.administradora adm
 where car.car_cd_administradora = adm.adm_cd_administradora
       and car.car_cd_caixa in (576785)
       and adm.adm_ds_bandeira_tef is not null
 group by adm.adm_cd_administradora
 order by 3,
          ordenacao,
          1

( CASE WHEN adm.adm_fl_tipo = 'D' then SUM (car.car_vl_cartao) END as total, ) Not working!

I need a cumulative total for when type is equal to 'C' and when it is equal to 'D'.Ir adding values where type equals' C' simplifying.

sql postgresql

asked by anonymous 28.05.2018 / 20:05

2 answers

convert 1 to active and 0 inactive within ng-repeat angular How to reference the copyright of the PdfSharp tool under the MIT license?

score 3 · Answer 1

The aggregate function sum() should be out of CASE , see:

SUM( CASE WHEN adm.adm_fl_tipo = 'D' THEN car.car_vl_cartao ELSE 0 END ) AS total

This will cause the value zero ( 0 ) to be added to the total result whenever the CASE WHEN condition is not true.

score 0 · Answer 2

RESOLVED:

sum(sum(car.car_vl_cartao) filter(where adm.adm_fl_tipo = 'D')) over(order by adm.adm_ds_administradora) as accum_debito,
sum(sum(car.car_vl_cartao) filter(where adm.adm_fl_tipo = 'C')) over(order by adm.adm_ds_administradora) as accum_credito,

In this way above, I was able to do the cumulative ones, thanks to all who contributed in some way.