CODEWITHSUNDEEP
sqlpostgresqladditioncalculated-columns
ziggy
ziggy

Reputation: 1538

SQL adding columns together

I have a PostgreSQL table of 7k records. each record has 3 a unique ID and 3 fields with it. childcares, shcools, hospitals. there are all integer fields. i want to add a new column and calculate the total amount of receptors (schools,childcares,hospitals) for each row. I thought this should be pretty straighforward with adding a column and doing an insert with a select but i am not getting the results i want

alter table site add total integer;

insert into site(total) select sum(schools+childcares+hospitals) as s from site;

i have also tried a group by id in the insert select statement

Upvotes: 0

Views: 156

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

You are looking for Update not Insert

Update site 
 set total = COALESCE(schools,0)+COALESCE(childcares,0)+COALESCE(hospitals,0)

Added COALESCE to handle NULL values.

Ex :

1 + 2 + NULL = NULL so to replace NULL with 0 I have used COALESCE.

Now it will be 1 + 2 + 0(NULL) = 3

Upvotes: 1

Related Questions