Update query with sum function in sql

How about post the DDL for the tables, some sample data for each table in the form of an insert statement with union all select statements , and what the output results of the query should be when run for the sample data. You can't use Group By as part of an Update query, if it meant to be part of the sub-query then you should enclose it in ' '. You should break the query into two parts.

Definitely need the table definitions to solve this one. With those, will only take a minute or two. ChrisM Work. It makes the statement far easier to check. Your statement will be something like this I will explain this issue more in the next section. Look at the example below:. The SUM function adds all values from the quantity column and returns the total as the result of the function. The name of the new result column i. As you see, the sum of values in the quantity column in the table product is 7.

This is the query:. In this case, the argument in the SUM function is an expression that calculates the total value for each product: the quantity of the product the value in the column quantity multiplied by its price the value in the column price. In this case, the category column is in the SELECT because we want to see the category for which the sum is calculated.

Next is the SUM function, which sums up the quantity values. Adding the GROUP BY clause means that products with the same value in the category column are put into one group; the sum is calculated for each group separately. For example:. Viewed 4k times. Improve this question. Michael Mormon. Michael Mormon Michael Mormon 3 3 gold badges 8 8 silver badges 16 16 bronze badges. You don't have a group by to use an aggregate but I think even with a proper aggregate it cannot but used in a set.

Add a comment. Active Oldest Votes. You can do the aggregate in a subsequent cte, and update from there. Improve this answer. Michael Mormon 3 3 gold badges 8 8 silver badges 16 16 bronze badges.

SqlZim SqlZim 2, 1 1 gold badge 9 9 silver badges 22 22 bronze badges. Sign up or log in Sign up using Google.