CODEWITHSUNDEEP
sqlpostgresql
user458
user458

Reputation: 409

How to add column and fill it using case in sql?

I have the following table

create table scientisttt (id integer, firstname varchar(100), lastname varchar(100));
        insert into scientisttt (id, firstname, lastname) values (1, 'albert', 'einstein');
        insert into scientisttt (id, firstname, lastname) values (2, 'isaac', 'newton');
        insert into scientisttt (id, firstname, lastname) values (3, 'marie', 'curie');
       insert into scientisttt (id, firstname, lastname) values (4, 'marie', 'curie');
        insert into scientisttt (id, firstname, lastname) values (5, 'isaac', 'newton');
        insert into scientisttt (id, firstname, lastname) values (6, 'isaac', 'newton');
        select * from scientisttt;

select count(*), firstname
from scientisttt
group by firstname;

select 
     * ,
     count(*) over (partition by firstname) as "Count"
into nam
from scientisttt; 

select * from nam;


select s.firstname, count into newtab from scientisttt as s left join namessss as n on s.firstname = n.firstname;

I know how to create a new table with an extra column like this:

select *,
case 
when count >= 3 then 'ok'
else 'bad'
end as newcol
into newtab2
from newtab;

But is there another way to do so?

Upvotes: 0

Views: 937

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can create a view:

create view v_scientisttt as
    select s.* ,
           (case when count(*) over (partition by firstname) > 3
                 then 'ok' else 'bad'
            end) as count
    from scientisttt s;

Anyone who uses the view can then see the correct values. There is no need to create a new table for this.

Upvotes: 2

Related Questions