Sum values of several lines under condition

Asked

Viewed 1,174 times

3

Good afternoon gentlemen, I have a query that returns to me some precise fields of their sum, only that in 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_type = ’D' then SUM(car.car_vl_card) END AS TOTAL,) Is not working! inserir a descrição da imagem aqui

I need a cumulative total for when the type equals 'C' and when it equals’D'. Add values where type equals 'C' and simplify.

sql postgresql

  • Even with Else not working.

  • puts adm_fl_tipo in group by

  • I did that, it changed nothing, because the types are the same but values differ.. I thought to use repetition loop maybe.. I don’t know

  • sets the environment here please: http://sqlfiddle.com/#! 15

2 answers

3

The function of aggregation sum() should stay out of of CASE, look at you:

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) is added to the total result whenever the condition of the CASE WHEN is not true.

0

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 make the accumulative, thank you to all who contributed in some way.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.