Petros Kyriakou Petros Kyriakou - 2 years ago 56
SQL Question

How can i SUM records from a table to another after multiplying two columns

I have a table called orderItems which has two columns, quantity and unit price.It also has a foreign key

in that very table.

I have another table called ordergroup with primary key
, which contains SavedTotal column which is the order total based on quantity * unit price for all order item rows that reference that

Now what i struggle with is the sql query that can get all order items based on a certain ordernumber and calculate the total cost.

I have managed to do the multiplication but i am missing the SUM, here is my sql query(based on SQL Server) so far.

UPDATE OrderGroupNew
set OrderGroupNew.SavedTotal = OrderItemNew.UnitPrice*OrderItemNew.QUANTITY
FROM OrderItemNew
inner join OrderGroupNew on OrderItemNew.OrderNumber=OrderGroupNew.OrderNumber

any help is appreciated

Answer Source
UPDATE OrderGroupNew
SET SavedTotal = (
    SELECT SUM(UnitPrice * Quantity)
    FROM OrderItemNew
    WHERE OrderNumber = OrderGroupNew.OrderNumber
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download