Add a column several times with different conditions

Question

Add a column several times with different conditions

Navigation

#1 by (2 votes)

1

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.

select 

case
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)



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


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

group by
tramos,
b.riesgototal

order by
tramos

sql postgresql

asked by Derlis Cabrera 21.12.2017 в 13:07

source

1 answer

Problem with value that Ajax returns when sending mail, even if it is successful Function MYSQL - IF

score 2 · Accepted 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,
       sum_capital
  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