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

Answer 1

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.

Answer 2

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

Answer 3

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 

Answer 4

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