How to create a new column from existing column?

Question

In a table AZ I have 3 columns say a,b,c || here c is alias of d || how to create a new column in same table say "e" from column c

select 
    a.lyl_id_no,
    sum(a.trn_tot_prc) as PURCH,
    sum(case when a.trn_dt > current_date - 365 then 1 else 0 end) cnt_trips_1yr , 
from abc a
group by 1

result :

a.lylid   purch  cnt_trips_lyr
123        12          4
242        10         1

But I need a new column in same table where it should say cnt_trips_1yr > 3 , cnt_trips_1yr > 2

Answer 1

If I understand correctly, just add more conditions. I think the easiest way is to use a subquery:

select a.*,
       (case when cnt_trips_1yr > 3 then 1 else 0 end) as IsCntGt3,
       (case when cnt_trips_1yr > 2 then 1 else 0 end) as IsCntGt2
from (select a.lyl_id_no,
             sum(a.trn_tot_prc) as PURCH,
             sum(case when a.trn_dt > current_date - 365 then 1 else 0 end) as cnt_trips_1yr
      from abc a
      group by a.lyl_id_no
     ) a;