从表中选择多个列但按组分组

Table name is OrderDetails and columns are given below

表名是OrderDetails，列如下

OrderDetailID || ProductID || ProductName || OrderQuantity

OrderDetailID || ProductID ||产品名称||订单数量

What I'm trying to achieve is, selecting multiple columns and Group By ProductID while having SUM of OrderQuantity.

我想要实现的是，在具有OrderQuantity的SUM时选择多个列和Group By ProductID。

 Select ProductID,ProductName,OrderQuantity Sum(OrderQuantity) 
 from OrderDetails Group By ProductID

But of course this codes gives an error, I have to add other column names to group by but that's not what I want and since my data has many items so results are unexpected that way.

但是当然这个代码会出错，我必须添加其他列名来分组，但这不是我想要的，因为我的数据有很多项，所以结果是出乎意料的。

 Sample Data Query : ProductID,ProductName,OrderQuantity from OrderDetails
 Results are below :

 ProductID     ProductName    OrderQuantity
    1001          abc               5
    1002          abc               23    (ProductNames can be same)
    2002          xyz               8
    3004          ytp               15
    4001          aze               19
    1001          abc               7     (2nd row of same ProductID)

Expected result :

预期结果：

 ProductID     ProductName    OrderQuantity
    1001          abc               12    (group by productID while summing)
    1002          abc               23    
    2002          xyz               8
    3004          ytp               15
    4001          aze               19

I can provide more details if needed, thanks in advance.

如果需要，我可以提供更多详细信息，提前感谢。

Question in short : Selecting multiple columns and Group By ProductID column since ProductName is not unique. While doing that also having sum of OrderQuantity column.

问题简而言之：选择多个列和Group By ProductID列，因为ProductName不是唯一的。这样做也有OrderQuantity列的总和。

7 个解决方案

#1

I use this trick for to group by one column when I have multiple columns selection :

当我有多个列选择时，我使用此技巧分组一列：

SELECT MAX(id) AS id,
    Nume,
    MAX(intrare) AS intrare, 
    MAX(iesire) AS iesire, 
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data 
FROM Produse 
GROUP BY Nume 
ORDER BY Nume

This works.

这很有效。

#2

Your Data

你的数据

DECLARE @OrderDetails TABLE 
(ProductID INT,ProductName VARCHAR(10), OrderQuantity INT)

INSERT INTO @OrderDetails VALUES
(1001,'abc',5),(1002,'abc',23),(2002,'xyz',8),(1002,'abc',1),
(3004,'ytp',15),(4001,'aze',19),(1001,'abc',7),(1001,'abc',2)

Query

询问

 Select ProductID,ProductName, Sum(OrderQuantity) Total
 from @OrderDetails 
 Group By ProductID ,ProductName

Result

结果

╔═══════════╦═════════════╦═══════╗
║ ProductID ║ ProductName ║ Total ║
╠═══════════╬═════════════╬═══════╣
║      1001 ║ abc         ║    14 ║
║      1002 ║ abc         ║    24 ║
║      4001 ║ aze         ║    19 ║
║      2002 ║ xyz         ║     8 ║
║      3004 ║ ytp         ║    15 ║
╚═══════════╩═════════════╩═══════╝

#3

You can try this :

你可以试试这个：

Select ProductID,ProductName,Sum(OrderQuantity) 
 from OrderDetails Group By ProductID, ProductName

You only required to Group By columns that doesn't come with aggregate function in Select clause. So you can just Group By ProductID and ProductName in this case.

您只需要在Select子句中使用不带聚合函数的列进行分组。因此，在这种情况下，您可以只按ProductID和ProductName进行分组。

#4

==EDIT==

== ==编辑

I checked your question again and have concluded this can't be done.

我再次检查了你的问题并得出结论，这是无法做到的。

ProductName is not unique, It must either be part of the Group By or excluded from your results.

ProductName不是唯一的，它必须是Group By的一部分或从结果中排除。

For example how would SQL present these results to you if you Group By only ProductID?

例如，如果仅按产品ID分组，SQL会如何向您显示这些结果？

ProductID | ProductName | OrderQuantity 
---------------------------------------
1234      | abc         | 1
1234      | def         | 1
1234      | ghi         | 1
1234      | jkl         | 1

#5

    WITH CTE_SUM AS (
      SELECT ProductID, Sum(OrderQuantity) AS TotalOrderQuantity 
      FROM OrderDetails GROUP BY ProductID
    )
    SELECT DISTINCT OrderDetails.ProductID, OrderDetails.ProductName, OrderDetails.OrderQuantity,CTE_SUM.TotalOrderQuantity 
    FROM 
    OrderDetails INNER JOIN CTE_SUM 
    ON OrderDetails.ProductID = CTE_SUM.ProductID

Please check if this works.

请检查一下是否有效。

#6

Though this is quite late for a solution, but you can try the below query. I assume you have a single table for all your data.

虽然这对解决方案来说已经很晚了，但您可以尝试以下查询。我假设你有一张表用于所有数据。

    SELECT OD.ProductID, OD.ProductName, CalQ.OrderQuantity
    FROM (SELECT DISTINCT ProductID, ProductName
        FROM OrderDetails) OD
    INNER JOIN (SELECT ProductID, OrderQuantity SUM(OrderQuantity) 
                FROM OrderDetails 
                GROUP BY ProductID) CalQ
    ON CalQ.ProductID = OD.ProductID

#7

-1

SELECT ProductID, ProductName, OrderQuantity, SUM(OrderQuantity) FROM OrderDetails WHERE(OrderQuantity) IN(SELECT SUM(OrderQuantity) FROM OrderDetails GROUP BY OrderDetails) GROUP BY ProductID, ProductName, OrderQuantity;

SELECT ProductID，ProductName，OrderQuantity，SUM（OrderQuantity）FROM OrderDetails WHERE（OrderQuantity）IN（SELECT SUM（OrderQuantity）FROM OrderDetails GROUP BY OrderDetails）GROUP BY ProductID，ProductName，OrderQuantity;

I used the above solution to solve a similar problem in Oracle12c.

我使用上面的解决方案来解决Oracle12c中的类似问题。

#1