CODEWITHSUNDEEP
sqlsql-server
AWGIS
AWGIS

Reputation: 103

How to divide two columns in same table and append the result to the end of the table in SQL

I am not very proficient with SQL and I am struggling to divide two table columns in SQL and append the result to the end of the same table.

The end result is to have the division averages appended and stored to the end of Table 1.

select RefKey, HPProD / HPSalD as 'HPAvgD' From [Table1]
select RefKey, HPProSD / HPSalSD as 'HPAvgSD' From [Table1]
select RefKey, HPProT / HPSalT as 'HPAvgT' From [Table1]
select RefKey, HPProF / HPSalF as 'HPAvgF' From [Table1]

I currently have this but it does not do the latter - instead the division is output into its own temp table.

Upvotes: 0

Views: 1434

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269613

Add computed columns. For instance:

alter table table1
     add PHAvgD as (HPProD / nullif(HPSalD));

You can repeat this for each new column.

The advantage of this approach is that the values are calculated when they are fetched -- so the calculations are always accurate for new rows and updated column values.

Upvotes: 1

Suraj Kumar
Suraj Kumar

Reputation: 5643

You can try this

create table [Table1] (RefKey int, HPProD int, HPSalD int)
insert into [Table1] values (1, 100, 20)

alter table [Table1] add HPAvgD int --If this colum is not in the table.
update [Table1] set HPAvgD = Case HPSalD when 0 then 0 else HPProD / HPSalD end

Live Demo

For other column apply same rule with comma in same query.

Upvotes: 1

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

You need add 4 columns for all four averages and run the update statement given below, 

UPDATE [Table1] 
    SET HPAvgD = Case when HPSalD =0 then 0 else HPProD / HPSalD end,
        HPAvgSD= Case when  HPSalD= 0 then 0 else HPProSD / HPSalSD end,
        HPAvgT = Case  when HPSalT= 0 then 0 else HPProT / HPSalT end,
        HPAvgF = Case  when HPSalF = 0 then 0 else HPProF / HPSalF end

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

do you find something below to create new table from existing one

insert into table1 (RefKey,HPSalD,HPSalSD,HPSalT,HPSalF)   
select RefKey,HPProD / NULLIF(HPSalD,0) as HPAvgD,
     HPProSD / NULLIF(HPSalSD,0) as HPAvgSD,
   HPProT / NULLIF(HPSalT,0) as HPAvgT,
  HPProF / NULLIF(HPSalF,0) as HPAvgF from table1

Upvotes: 1

Related Questions