CODEWITHSUNDEEP
sqlsql-server
Petros Kyriakou
Petros Kyriakou

Reputation: 5343

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 ordernumber in that very table.

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

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

Upvotes: 0

Views: 33

Answers (2)

Sébastien Pertus
Sébastien Pertus

Reputation: 715

You can use a TVP as well :

;With o as (
        select OrderItemNew.OrderNumber as OrderNumber, 
        SUM(OrderItemNew.UnitPrice*OrderItemNew.QUANTITY) as OrderSum
        Group by OrderItemNew.OrderNumber)
UPDATE OrderGroupNew
set OrderGroupNew.SavedTotal = o.OrderSum
FROM o
INNER JOIN OrderGroupNew on o.OrderNumber=OrderGroupNew.OrderNumber

Well the 1st answer is correct too. Choose the best in term of performance :) (dont know which will be the best, to be honest ! )

Upvotes: 0

Eric
Eric

Reputation: 3257

UPDATE OrderGroupNew
SET SavedTotal = (
    SELECT SUM(UnitPrice * Quantity)
    FROM OrderItemNew
    WHERE OrderNumber = OrderGroupNew.OrderNumber
)

Upvotes: 1

Related Questions