CODEWITHSUNDEEP
sqlsum
Marko
Marko

Reputation: 72222

Calculating the SUM of (Quantity*Price) from 2 different tables

I have two tables as follows

PRODUCT table

Id | Name | Price

And an ORDERITEM table

Id | OrderId | ProductId | Quantity

What I'm trying to do is, calculate the subtotal price for each product (Quantity*Price) then SUM the TOTAL value for the entire order..

I'm trying something like this

SELECT Id, SUM(Quantity * (select Price from Product where Id = Id)) as qty
FROM OrderItem o
WHERE OrderId = @OrderId

But of course that doesn't work :)

Any help appreciated!

EDIT: I only want to show the grand total for the entire order, so basically the sum of Quantity*Price for every row in OrderItem. Here's some sample data.

Sample Data

TABLE Product

Id     Name            Price  
1      Tomatoes        20.09    
4      Cucumbers       27.72    
5      Oranges         21.13    
6      Lemons          20.05
7      Apples          12.05

Table OrderItem

Id         OrderId        ProductId        Quantity
151        883            1                22
152        883            4                11
153        883            5                8
154        883            6                62

M

Upvotes: 10

Views: 118511

Answers (5)

Alican Dönmez
Alican Dönmez

Reputation: 1

I had the same problem as Marko and come across a solution like this:

/*Create a Table*/
CREATE TABLE tableGrandTotal
(
columnGrandtotal int
)

/*Create a Stored Procedure*/
CREATE PROCEDURE GetGrandTotal
AS

/*Delete the 'tableGrandTotal' table for another usage of the stored procedure*/
DROP TABLE tableGrandTotal

/*Create a new Table which will include just one column*/
CREATE TABLE tableGrandTotal
(
columnGrandtotal int
)

/*Insert the query which returns subtotal for each orderitem row into tableGrandTotal*/
INSERT INTO tableGrandTotal
    SELECT oi.Quantity * p.Price AS columnGrandTotal
        FROM OrderItem oi
        JOIN Product p ON oi.Id = p.Id

/*And return the sum of columnGrandTotal from the newly created table*/    
SELECT SUM(columnGrandTotal) as [Grand Total]
    FROM tableGrandTotal

And just simply use the GetGrandTotal Stored Procedure to retrieve the Grand Total :)

EXEC GetGrandTotal

Upvotes: 0

Beth
Beth

Reputation: 9607

select orderID, sum(subtotal) as order_total from
(
    select orderID, productID, price, qty, price * qty as subtotal
    from product p inner join orderitem o on p.id = o.productID
    where o.orderID = @orderID
) t
group by orderID

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

Use:

  SELECT oi.orderid,
         SUM(oi.quantity * p.price) AS grand_total,
    FROM ORDERITEM oi
    JOIN PRODUCT p ON p.id = oi.productid
   WHERE oi.orderid = @OrderId
GROUP BY oi.orderid

Mind that if either oi.quantity or p.price is null, the SUM will return NULL.

Upvotes: 22

Matt M
Matt M

Reputation: 3779

I think this is along the lines of what you're looking for. It appears that you want to see the orderid, the subtotal for each item in the order and the total amount for the order.

select o1.orderID, o1.subtotal, sum(o2.UnitPrice * o2.Quantity) as order_total from
(
    select o.orderID, o.price * o.qty as subtotal
    from product p inner join orderitem o on p.ProductID= o.productID
    where o.orderID = @OrderId
)as o1
inner join orderitem o2 on o1.OrderID = o2.OrderID
group by o1.orderID, o1.subtotal

Upvotes: 1

Randy
Randy

Reputation: 16677

i think this - including null value = 0

 SELECT oi.id, 
         SUM(nvl(oi.quantity,0) * nvl(p.price,0)) AS total_qty 
    FROM ORDERITEM oi 
    JOIN PRODUCT p ON p.id = oi.productid 
   WHERE oi.orderid = @OrderId 
GROUP BY oi.id

Upvotes: 1

Related Questions