How do I do this join without including ItemID in the group by?

Question

I am trying to find the sum of each order then group by the order ID. I had to use items.ItemID to join the 2 tables but I cannot get the sum of each order if I place ItemID in the group by statement.

This is what I tried:

SELECT Orders.OrderID, Items.ItemID, Sum(Items.Price) AS SumPrice, Format(SumPrice*1.06,"Currency") AS TotalPrice
FROM Orders, Items
WHERE Items.ItemID=Orders.ItemID
GROUP BY OrderID;

I got the error "Your query does not include "ItemID" as part of an aggregate function."

Answer 1

the SQL group can have multiple items.ItemId values. You need to specify which ItemId you want from the group by setting MAX(items.itemId) or MIN (items.ItemId) (or AVG or SUM, etc.). or remove Items.Itemid from your select statement:

SELECT Orders.OrderID, Sum(Items.Price) AS SumPrice, 
Format(SumPrice*1.06,"Currency") AS TotalPrice FROM Orders, Items WHERE 
Items.ItemID=Orders.ItemID GROUP BY OrderID;