How to get the total amount in MS Access SQL?

Question

I use the following query to get OrderID:

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

How can I get the total amount (add all Total with the same OrderID) for each order ID?

Answer 1

You can use the selected form for your selection and group by

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

I am only new to SQL, but I think this is a solution.

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

Hope this helps.