By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,469 Members | 1,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,469 IT Pros & Developers. It's quick & easy.

Sum one Field base on matching of multiple column

P: 1
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?
2 Weeks Ago #1
Share this Question
Share on Google+
2 Replies


P: 9
Here is the solution to your problem statement using Alias :

Expand|Select|Wrap|Line Numbers
  1. update p set p.TotalAmount = (select sum(amount) from ProcessTable as t where t.NAME = p.NAME
  2. AND t.Company = p.Company
  3. AND t.Age = p.Age
  4. AND t.Gender = p.Gender)
  5. FROM ProcessTable as p
  6.  
1 Week Ago #2

Rabbit
Expert Mod 10K+
P: 12,401
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.
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.