Results 1 to 9 of 9
  1. #1
    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
    05-001 1353821.0000
    05-054 565658.0000
    05-097 468399.0000
    05-117 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!

    Mike
    Last edited by micjay22; 10-27-11 at 16:41.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    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
    05-001	' '	'A'		10
    05-001	'Q'	'Z'		100
    05-001	' '	'A'		1000
    06-000	'Z '	' '		10000
    
    result UPDATE will be:
    WBS1	WBS2	Status	Fee
    05-001	' '	'A'		1010
    05-001	'Q'	'Z'		1010
    05-001	' '	'A'		1010
    06-000	'Z '	' '		NULL
    
    result after running UPDATE again:
    WBS1	WBS2	Status	Fee
    05-001	' '	'A'		2020
    05-001	'Q'	'Z'		2020
    05-001	' '	'A'		2020
    06-000	'Z '	' '		NULL
    Can you explain what you want to accomplish with it? The UPDATE script will overwrite the value in the Fee column in ALL the records of PR with the sum of the fees of related records (same WBS1) and with WBS2 = ' ' and status = 'A', when no such records can be found it overwrites those fee columns with NULLs.

    "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
    05-001	' '	'A'		10
    05-001	'Q'	'Z'		100
    05-001	' '	'A'		1000
    06-000	'Z '	' '		10000
    
    result UPDATE will be:
    WBS1	WBS2	Status	Fee
    05-001	' '	'A'		1010
    05-001	'Q'	'Z'		1010
    05-001	' '	'A'		1010
    06-000	'Z '	' '		10000
    
    result after running UPDATE again:
    WBS1	WBS2	Status	Fee
    05-001	' '	'A'		2020
    05-001	'Q'	'Z'		2020
    05-001	' '	'A'		2020
    06-000	'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

  3. #3
    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 sub-phases 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.11-050 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!
    Attached Thumbnails Attached Thumbnails WBS2.JPG   WBS1.JPG   PR table result.JPG  
    Last edited by micjay22; 10-28-11 at 10:35.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This 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

  5. #5
    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."

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    You 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'
    The "#" in front of the table name means it is a (local) temporary table in my test database. When I log off (end the session), all tables starting with # will be automatically deleted from my database, leaving a clean system.

    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

  7. #7
    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!

  8. #8
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I think you renamed the table PR (so you can update same column later),
    I created a temporary table #PR in my own test database to test the result of my query. That is all there is to it. See my previous post why temporary tables are handy.

    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, when NOT(PR.WBS2 = ' ' and PR.status = 'A') selects only the "active" jobs.
    - yes
    I'm not sure why do you need to include the search statement again outside of the subquery.
    The two search criteria are each other's opposite. You are summing values from the column "fee" and storing the result in the very same column. You must find a way to avoid overwriting the base data with the sum. Remember your first results?

    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 now
    Last edited by Wim; 11-01-11 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

  9. #9
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •