首页 文章

从2个不同的表中计算（数量*价格）的和

提问于
浏览 次
8

我有两张表如下

PRODUCT 表

Id | Name | Price

还有一个 ORDERITEM 表

Id | OrderId | ProductId | Quantity

我要做的是，计算每个产品的小计价格（数量*价格），然后为整个订单计算总计 Value .

我正在尝试这样的事情

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

但当然这不起作用:)

任何帮助赞赏！

EDIT: 我只想显示整个订单的总计，所以基本上是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

中号

sql sum

5 回答

  • 19

    使用：

    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 为空，则SUM将返回NULL .

    回复于
  • 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
    回复于
  • 0

    我认为这与你所寻找的一致 . 您似乎想要查看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
    回复于
  • 1
    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
    回复于
  • 1

    我遇到了与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

    只需使用GetGrandTotal存储过程来检索总计:)

    EXEC GetGrandTotal
    回复于

相关问题