SQL Server: Update Column with Sum of Other Columns When-CodePudding

Let's say I have the table structure as shown below. Which SQL script can I use to update the column ,Size2, with the Sum of column, Size, when the Animals are equal? Platform is SQL Server. I want to have a column Size2 that is the sum of the Size when animal names equal each other. I'm struggling to put the script below into an update statement but please see the select statement.

CREATE TABLE table1 (

    Size    nvarchar(50),
    Animals nvarchar(250),
    Part    int,
    Size2   nvarchar(250)
)

Size	Animals	Part
30	Pig	1
60	Tiger	1
10	Tiger	2
30	Pig	2
90	Lion	1
20	Lion	2
10	Lion	3

This is the desired output, I do not have a Size2 column in the table yet.

Size	Animals	Part	Size2
30	Pig	1	60
60	Tiger	1	70
10	Tiger	2	70
30	Pig	2	60
90	Lion	1	120
20	Lion	2	120
10	Lion	3	120

So far I have:

SELECT
    Animals,
    SUM( TRY_CONVERT( int, Size )

FROM
    Table1

WHERE
    Part IS NOT NULL

GROUP BY
    Animals

CodePudding user response：

For select statement, you can use PARTITION BY clause

This is select statement

SELECT Size, Animals, Part,
SUM(CAST(size as int)) OVER(PARTITION BY Animals) As total_size
FROM table1
WHERE part IS NOT NULL;

For update statement

UPDATE table1
SET table1.size2 = table2.total_size
FROM
    table1
    INNER JOIN
    (SELECT Size, Animals, Part,
    SUM(CAST(size as int)) OVER(PARTITION BY Animals) As total_size
    FROM table1
    WHERE part IS NOT NULL) AS table2
    ON table1.size = table2.size AND
    table1.animals = table2.animals AND
    table1.part = table2.part;

Note: UPDATE statement above will only update size2 column where part is not null.

If you want to update all size2 column even though part is null, remove last line(table1.part = table2.part) will do

CodePudding user response：

The desired result can be achieved by following SQL

SELECT t.Size, t.Animal, t.Part, t2.AnimalSum
FROM table1 t RIGHT JOIN
(SELECT Animal, Sum(convert(int,Size)) AS AnimalSum
FROM table1
group BY Animal) AS t2 ON t.Animal = t2.Animal

so the update can be easily done with an update using above query statement.

CodePudding user response：

From your query, you can update with this

UPDATE t1
SET 
t1.Size2 = t2.SumSize
FROM Table1 t1
INNER JOIN
(SELECT Animals, SUM(TRY_CONVERT(int,Size)) as SumSize
FROM Table1
WHERE Part IS NOT NULL
GROUP BY Animals) t2
ON t1.Animals = t2.Animals