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."

CodePudding user response：

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;

CodePudding user response：

There are two options:

1. Remove ItemID in the SELECT clause. The JOIN will associate the Orders with Items.
2. Include ItemID in the SELECT clause. All columns/fields must be in the GROUP BY or use an aggregate function such as Count or Max.
3. Use a subquery that has the GROUP BY and return ItemID at the outer layer. This is primarily useful for returning extra information from related columns that you do not want to include in the GROUP BY.

Example for #2

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

Example for #3

SELECT Orders.OrderID,
       Orders.ItemID,
       -- Use the count from subquery to get SumPrice
       Orders.CountOrderID * Items.Price AS SumPrice,
       Format(Orders.CountOrderID * Items.Price,"Currency") AS TotalPrice
       -- Optional: include extra info from Items
FROM (
    -- Subquery with GROUP BY
    SELECT Orders.OrderID,
           Orders.ItemID,
           Count(Orders.OrderID) AS CountOrderID
    FROM Orders
    GROUP BY Orders.OrderID, Orders.ItemID
    ) AS Orders JOIN Items ON Items.ItemID = Orders.ItemID;