CODEWITHSUNDEEP
sqlsql-serversql-update
mHelpMe
mHelpMe

Reputation: 6668

update and using sum to calculate column value

I am using sql-server. I have a table which contains the value of a list of companies. I wish to workout the % of each company as part of this list. See example below

name   value    wgt%
abc    10
plm    15
xyz    25

So above is what I have, below is what I would like,

name   value    wgt%
abc    10       20
plm    15       30
xyz    25       50

I tried something like:

update D_COMP_VALUES
set wgt = value / sum(value)

Upvotes: 2

Views: 1145

Answers (5)

Mureinik
Mureinik

Reputation: 311438

An update-join seems in order:

UPDATE     d
SET        wgt = value * 100.0 / sumvalue 
FROM       d_comp_values d
CROSS JOIN (SELECT SUM(value) AS sumvalue FROM d_comp_values) agg

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can do a simple update:

UPDATE D_COMP_VALUES
SET wgt = value * 100 / (SELECT SUM(value) FROM D_COMP_VALUES)

SQL Fiddle Demo

Upvotes: 1

Marshall Tigerus
Marshall Tigerus

Reputation: 3764

I'll leave the implementation to you, but here's the way I would do this.

  1. Create a variable for the sum.
  2. Calculate the sum from a query and insert it into the variable
  3. Update the base table setting wgt = value/variable

That said, storing this information in the table is a bad idea, and you should be calculating it or using a view.

Upvotes: 0

jarlh
jarlh

Reputation: 44776

Use a sub-select to get the total sum:

update D_COMP_VALUES
set wgt = 100 * value / (select sum(value) from D_COMP_VALUES)

But it's normally a bad idea to store computed values. Create a view instead, that never will have inconsistent data!

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I would approach this using window functions:

with toupdate as
      select cv.*, sum(value) over () as sumvalue
      from d_comp_values
     )
update toupdate
    set wgt = value / sumvalue;

Note that SQL Server does integer division, so if value is an integer (as suggested by your sample data), you will probably get zero unless you convert to another numeric representation:

set wgt = value * 1.0 / sumvalue;

Upvotes: 1

Related Questions