Add a column several times with different conditions


I am working on postgres and I would like you to help me add a column several times with different conditions for example I have my column days that are arrears of credits and the capital column that generates a credit, I need to group them as the table where they are grouped by greater than X days.


when bpatra > 30  then 'MAYOR A 30'
when bpatra > 60  then 'MAYOR A 60'
when bpatra > 90  then 'MAYOR A 90'
when bpatra > 150  then 'MAYOR A 150'
when bpatra > 180  then 'MAYOR A 180'
when bpatra > 270  then 'MAYOR A 270'
else null end as tramos,

round(sum(bpsalp + bpsali -bpadev -bpsusp))as riesgo,
round(((sum(bpsalp + bpsali -bpadev -bpsusp)/b.riesgototal)*100),2)

(select sum(bpsalp + bpsali -bpadev -bpsusp) riesgototal from fsd012co where bpfec = current_date and bptip <> 'P' And bpmon = 6900) b

(bpfec = current_date)and
(bpmon = 6900) and
(bptip <>'P' )

group by

order by
asked by Derlis Cabrera 21.12.2017 в 13:07

1 answer


I will take your simplified example to illustrate how it can be done.

The idea is to use an expression CASE . But be careful, that the order of the conditions is important. Conditions for higher amounts must appear first. Otherwise, almost all quantities will be added under dias > 30 .

Additionally, instead of translating the conditions of CASE directly into text, I translate it into a logical ID that can be used later for ORDER BY :

select case tramo_id
            when 1 then 'vigentes'
            when 2 then 'MAYOR A 30'
            when 3 then 'MAYOR A 60'
            when 4 then 'MAYOR A 90'
            when 5 then 'MAYOR A 120'
       end as tramos,
  from (select case when dias > 120 then 5
                    when dias > 90  then 4
                    when dias > 60  then 3
                    when dias > 30  then 2
                                    else 1
               end as tramo_id,
               sum(capital) as sum_capital
          from tbl
         group by tramo_id) t
 order by tramo_id
answered by 21.12.2017 / 14:31