Sum one Field base on matching of multiple column

[davidlowcy]

Hi,

I have a table with below 6 columns, I need to sum the amount together if Name + Campany + age + Gender are the same, and the result needs to keep at TotalAmount Column.

Name Company Age Gender Amount TotalAmount
Nick A 40 M 800
Nick A 40 M 1200
Nick B 40 M 400
Nick B 44 M 500
Belle A 40 F 600
Belle A 35 F 900
Moon C 59 F 500
Moon C 59 F 600

My desire result are below

Name Company Age Gender Amount TotalAmount
Nick A 40 M 800 2000
Nick A 40 M 1200 2000
Nick B 40 M 400 400
Nick B 44 M 500 500
Belle A 40 F 600 600
Belle A 35 F 900 900
Moon C 59 F 500 1100
Moon C 59 F 600 1100


I try below statement
update ProcessTable set TotalAmount = (select sum(Amount) from ProcessTable Group by Name, Company, Age, Gender)

But it give me error
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."

Can you teach me how to solve the issue?

[Ishan Shah]

Here is the solution to your problem statement using Alias :

update p set p.TotalAmount = (select sum(amount) from ProcessTable as t where t.NAME = p.NAME
AND t.Company = p.Company
AND t.Age = p.Age
AND t.Gender = p.Gender)
FROM ProcessTable as p

[Rabbit]

You should avoid keeping aggregates stored in a table like this. Instead, just use a view or query to get the aggregate data when needed. This way, your data is always up to date and won't get out of sync.