SQL 从 2 个不同的表中计算（数量*价格）的总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3506332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating the SUM of (Quantity*Price) from 2 different tables
提问by Marko
I have two tables as follows
我有两个表如下
PRODUCTtable
PRODUCT桌子
Id | Name | Price
And an ORDERITEMtable
还有一张ORDERITEM桌子
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.
编辑：我只想显示整个订单的总计，所以基本上是 OrderItem 中每一行的 Quantity*Price 的总和。这是一些示例数据。
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
米
回答by OMG Ponies
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.quantityor p.priceis null, the SUM will return NULL.
请注意，如果oi.quantity或p.price为空，则 SUM 将返回 NULL。
回答by Randy
i think this - including null value = 0
我认为这 - 包括空值 = 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
回答by Matt M
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.
我认为这与您正在寻找的内容一致。看来您想查看订单 ID、订单中每件商品的小计以及订单的总金额。
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
回答by Alican D?nmez
I had the same problem as Marko and come across a solution like this:
我遇到了与 Marko 相同的问题，并遇到了这样的解决方案：
/*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 :)
只需简单地使用 GetGrandTotal 存储过程来检索总计 :)
EXEC GetGrandTotal
回答by Beth
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
