如何在MS Access SQL中获取总金额

Question

我使用以下查询来获取订单ID：

SELECT OrderItem.ID
     , ProductID
     , OrderID
     , Quantity
     , P.Title
     , P.CurrentPrice
     , P.ID
     , (P.CurrentPrice* OrderItem.Quantity) AS Total
FROM OrderItem
INNER JOIN Product AS P
   ON OrderItem.ProductID = P.ID

如何获取每个订单ID的总金额(将具有相同订单ID的所有总计相加(？

Answer 1

您可以使用选择表单进行选择和分组依据

select OrderID, sum(Total) 
from (
SELECT 
    OrderItem.ID
    , ProductID
    , OrderID
    , Quantity
    , P.Title
    ,P.CurrentPrice
    , P.ID
    , (P.CurrentPrice* OrderItem.Quantity) AS Total
FROM OrderItem 
INNER JOIN Product AS P ON OrderItem.ProductID = P.ID
) t 
group by OrderId 

Answer 2

我只是SQL的新手，但我认为这是解决方案。

SELECT OrderItem.ID, ProductID, OrderID, Sum(Quantity) AS Sum of Quantity, P.Title,P.CurrentPrice, P.ID, (P.CurrentPrice* OrderItem.Quantity) AS Total
FROM OrderItem INNER JOIN Product AS P ON OrderItem.ProductID = P.ID GROUP BY OrderID

希望这有帮助。