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
Использование:
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
Имейте в виду, что если либо oi.quantity
, либо p.price
равно нулю, СУММ вернет NULL.
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
Я думаю, что это именно то, что вы ищете. Похоже, вы хотите увидеть идентификатор заказа, промежуточный итог для каждого элемента в заказе и общую сумму заказа.
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