[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

How do I perform a calculation in an update query?

I need to perform a query that first calculates the total weight for a category. There are not a set number of categories. Then I need to take the total weight for each category and update the weights withing that category by dividing the current value by the sum. Any help is appreciated on this.
UPDATE Testing SET weight = weight/b.weight
FROM Testing a
	inner join
	(
		SELECT SUM(weight) as 'weight' FROM Testing GROUP BY category

	) b on a.Category = b.Category

Open in new window

0
nisupport
Asked:
nisupport
1 Solution
 
HainKurtSr. System AnalystCommented:
can you please give some sample data before and after the query...

I could not understand the logic here...
0
 
Reza RadCommented:
this query seems to be correct!
what error do you receive when run this statement?
try code below it's with alias names now

UPDATE Testing SET a.weight = a.weight/b.weight
FROM Testing a
        inner join
        (
                SELECT SUM(weight) as 'weight' FROM Testing GROUP BY category

        ) b on a.Category = b.Category

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello nisupport,

UPDATE a
SET a.weight = a.weight/b.weight
FROM Testing a
inner join
(
        SELECT Category, SUM(weight) as 'weight' FROM Testing GROUP BY category
) b on a.Category = b.Category




Regards,

aneeshattingal
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now