Results 1 to 9 of 9

102711, 16:19 #1Registered User
 Join Date
 Oct 2011
 Posts
 7
Unanswered: Group summing and updating the same column
Hello, I'm new to MS SQL and I'm trying to do some simple query to do a total then update the same column but getting the following error:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated."
I have a 'fee' column and I would like to the get the sum on the WBS1 level. (There's a WBS2 sub level, WBS1 is the sum of WBS2).
I can get the select statement working simply to show the desired final result by running the following query:
Select wbs1,
wbs2,
sum(PR.Fee) as 'total_fee'
from PR
Where PR.WBS2 = ' '
and PR.status = 'A'
group by PR.WBS1, PR.WBS2
order by WBS1, WBS2
That'll give me something like this:
wbs1 wbs2 total_fee
05001 1353821.0000
05054 565658.0000
05097 468399.0000
05117 373500.0000
But when I try to update the same fee column with the following query:
update PR
set Fee = (select sum (Fee) from PR
Where PR.WBS2 = ' '
and PR.status = 'A'
group by PR.WBS1
)
from PR
I got the mentioned error. Is there a way to sum up and update the same column?
Thank you for any input!
MikeLast edited by micjay22; 102711 at 16:41.

102711, 18:38 #2Registered User
 Join Date
 Nov 2004
 Posts
 1,427
Provided Answers: 4Code:update U set Fee = (select sum(PR.Fee) from PR Where U.WBS1 = PR.WBS1 and PR.WBS2 = ' ' and PR.status = 'A' ) from PR as U start situation: WBS1 WBS2 Status Fee 05001 ' ' 'A' 10 05001 'Q' 'Z' 100 05001 ' ' 'A' 1000 06000 'Z ' ' ' 10000 result UPDATE will be: WBS1 WBS2 Status Fee 05001 ' ' 'A' 1010 05001 'Q' 'Z' 1010 05001 ' ' 'A' 1010 06000 'Z ' ' ' NULL result after running UPDATE again: WBS1 WBS2 Status Fee 05001 ' ' 'A' 2020 05001 'Q' 'Z' 2020 05001 ' ' 'A' 2020 06000 'Z ' ' ' NULL
"ALL the records of PR", including those with WBS2 = ' ' and status = 'A'.
This will be faster, and will not set columns to NULL
Code:update U set U.Fee = T.FeeSum from #PR as U INNER JOIN (select #PR.WBS1, sum(#PR.Fee) as FeeSum from #PR Where #PR.WBS2 = ' ' and #PR.status = 'A' GROUP BY #PR.WBS1 ) as T ON U.WBS1 = T.WBS1 start situation: WBS1 WBS2 Status Fee 05001 ' ' 'A' 10 05001 'Q' 'Z' 100 05001 ' ' 'A' 1000 06000 'Z ' ' ' 10000 result UPDATE will be: WBS1 WBS2 Status Fee 05001 ' ' 'A' 1010 05001 'Q' 'Z' 1010 05001 ' ' 'A' 1010 06000 'Z ' ' ' 10000 result after running UPDATE again: WBS1 WBS2 Status Fee 05001 ' ' 'A' 2020 05001 'Q' 'Z' 2020 05001 ' ' 'A' 2020 06000 'Z ' ' ' 10000
With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3  not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3  in at least two programming languages

102811, 10:27 #3Registered User
 Join Date
 Oct 2011
 Posts
 7
Hi Wim, first of all, thank you for taking your time out to respond my post, I truly appreciate it.
You are exactly right, when I ran my update script, it overwrote the fee column with a the same number for all of my records. Which is not what I intended it to do. Luckily I was in a test database.
It's hard to explain without any visual, so I'm including some screen shots. We have a front end database GUI for users to enter data called Vision. In it we store project fee information. Each job is assigned an overall level (WBS1) and subphases levels (WBS2). Because of additional customization of the grid data in the phase level, the system doesn't auto total the sum natively. I wrote a script to update the compensation (fee column) by taking the sum all grid data's compensation from another table(see the attached screenshot called WBS2). Now I just need to take the total sum of compensation (fee) of all the different phases and update the same column on the WBS1 level for each particular job (see screenshot called WBS1)
I got it working on the WBS2 level, but cannot get it right on the WBS1 level, I think because I'm summing and updating the same column?
Below is the script to get the total on WBS2 level, I want to do the same on the parent WBS1 level.
Updating WBS2 level
In 'Projects_fees' table, get the sum of 'custCompensation' for all different WBS2 phases (ex.11050 phase 050)
Update the corresponding columns 'Fee' in the 'PR' table
on status = A only in the PR table
UPDATE pr
SET Fee =
(select sum(projects_fees.CustCompensation)
from Projects_Fees
where PR.WBS1 = Projects_Fees.WBS1
and pr.wbs2 = Projects_Fees.WBS2
and pr.status = 'A'
group by Projects_Fees.WBS1, Projects_Fees.WBS2
)
WHERE EXISTS
(select sum(projects_fees.CustCompensation)
from Projects_Fees
where PR.WBS1 = Projects_Fees.WBS1
and pr.wbs2 = Projects_Fees.WBS2
and pr.status = 'A'
group by Projects_Fees.WBS1, Projects_Fees.WBS2
)
I also included a screenshot of select statement to check my data from the PR table (see screenshot PR table result) as you can see, I screwed up the data pretty bad, WBS2 = ' ' is the key to find the parent level, because parent level doesn't have a phase number in WBS2.
Hopefully I haven't lost you, again any pointers is highly appreciated. Thanks!Last edited by micjay22; 102811 at 10:35.

102811, 16:07 #4Registered User
 Join Date
 Nov 2004
 Posts
 1,427
Provided Answers: 4This should work:
Code:update U set U.Fee = T.FeeSum from #PR as U LEFT OUTER JOIN (select #PR.WBS1, sum(#PR.Fee) as FeeSum from #PR Where NOT(#PR.WBS2 = ' ' and #PR.status = 'A') GROUP BY #PR.WBS1 ) as T ON U.WBS1 = T.WBS1 Where U.WBS2 = ' ' and U.status = 'A'
With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3  not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3  in at least two programming languages

102811, 16:52 #5Registered User
 Join Date
 Oct 2011
 Posts
 7
Thank you for your input.
I'm not sure why you added # in front of PR, it looks like unknown reference to SQL, when I've taken the # out, I get an error of "Cannot insert the value NULL into column 'Fee', table 'Test2.dbo.PR'; column does not allow nulls. UPDATE fails.
The statement has been terminated."

102911, 11:24 #6Registered User
 Join Date
 Nov 2004
 Posts
 1,427
Provided Answers: 4You won't get that error any more with this:
Code:update U set U.Fee = COALESCE(T.FeeSum, 0) from #PR as U LEFT OUTER JOIN (select #PR.WBS1, sum(#PR.Fee) as FeeSum from #PR Where NOT(#PR.WBS2 = ' ' and #PR.status = 'A') GROUP BY #PR.WBS1 ) as T ON U.WBS1 = T.WBS1 Where U.WBS2 = ' ' and U.status = 'A'
I find it very useful for testing many different things and at the end of the day (or the next day) still have a clean system.With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3  not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3  in at least two programming languages

103111, 14:31 #7Registered User
 Join Date
 Oct 2011
 Posts
 7
Hi Wim, I would like to let you know that your script appears to be working. The total added up just fine on the WBS1 level. You are such a godsend. I will monitor on a couple of more jobs in the test database for a few more days before conducting it to live. According to my limited SQL knowledge, I think you renamed the table PR (so you can update same column later), and matched it up with a logical table you created with the sum aggregate expression for 'fee' column in WBS2 level for each active jobs. You used Coalesce statement because there is null data in the result set and it will error out if this is not specified? Also you told it to it return the sum first but if it's null then return 0? I'm not sure why do you need to include the search statement again outside of the subquery. If you have any free time and and don't mind to educate, I would love to hear your thought process. Again, thank you for your help!

110111, 09:53 #8Registered User
 Join Date
 Nov 2004
 Posts
 1,427
Provided Answers: 4I think you renamed the table PR (so you can update same column later),
I removed the '#' to make it more clear.
Code:update U set U.Fee = COALESCE(T.FeeSum, 0) from PR as U INNER JOIN (select PR.WBS1, sum(PR.Fee) as FeeSum from PR Where NOT(PR.WBS2 = ' ' and PR.status = 'A') GROUP BY PR.WBS1 ) as T ON U.WBS1 = T.WBS1 Where U.WBS2 = ' ' and U.status = 'A'
 and matched it up with a logical table you created with the sum aggregate expression for 'fee' column in WBS2 level for each active jobs.
 You used Coalesce statement because there is null data in the result set and it will error out if this is not specified? Also you told it to it return the sum first but if it's null then return 0?
 yes
I'm not sure why do you need to include the search statement again outside of the subquery.
The way it is handled is by first calculating the sum of only the "active" records (Where NOT(PR.WBS2 = ' ' and PR.status = 'A')) and then storing the sum of all those records in the corresponding "inactive" records (Where U.WBS2 = ' ' and U.status = 'A').
Thanks for the beer. Earned beers is 10 nowLast edited by Wim; 110111 at 16:33.
With kind regards . . . . . SQL Server 2000/2005/2012
Wim
Grabel's Law: 2 is not equal to 3  not even for very large values of 2.
Pat Phelan's Law: 2 very definitely CAN equal 3  in at least two programming languages

110111, 10:27 #9Registered User
 Join Date
 Oct 2011
 Posts
 7
Sorry, but I'm still a little confused...
Logically thinking, isn't Where NOT(PR.WBS2 = ' 'and PR.status = 'A') means looking for the opposite of active status which is inactive, and opposite of WBS2= ' ' which means on phase level? The original request is to summing up the fee for all active jobs, why do you store the results in the inactive jobs?