我如何做这个连接不包括ItemID在组by?

Question

我试图找到每个订单的总和，然后按订单ID分组。我不得不使用物品。ItemID连接两个表，但如果我将ItemID放在group by语句中，我无法获得每个订单的总和。

这是我尝试过的:

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;

我得到了错误"你的查询不包括"ItemID"作为集合功能的一部分。">

Answer 1

SQL组可以有多个项。ItemId值。您需要通过设置MAX(items.itemId)或MIN (items.itemId)(或AVG或SUM等)来指定您想要从组中获取的ItemId。或移除项目。select语句中的Itemid:

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;

Answer 2

有三个选项:

1. 删除SELECT子句中的ItemID。从您的模式中，不清楚是否有多个行用于相同的OrderID。
2. SELECT条款中包含ItemID。所有列/字段必须在GROUP BY中，或者使用Count或Max等聚合函数。
3. 使用具有GROUP BY并在外层返回ItemID的子查询。这主要用于从相关列返回您不希望包含在GROUP BY中的额外信息。

#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;

#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;