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

The table name is "OrderDetails" and columns are given below:

表名是“OrderDetails”,列如下:

OrderDetailID || ProductID || ProductName || OrderQuantity

I'm trying to select multiple columns and Group By ProductID while having SUM of OrderQuantity.

我正在尝试选择多个列和Group By ProductID,同时具有OrderQuantity的SUM。

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

But of course this code 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

OrderDetails中的ProductID,ProductName,OrderQuantity

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

How do I select multiple columns and Group By ProductID column since ProductName is not unique?

如何选择多列和Group By ProductID列,因为ProductName不是唯一的?

While doing that, also get the sum of the OrderQuantity column.

在这样做的同时,也获得OrderQuantity列的总和。

7 个解决方案

#1

I use this trick for to group by one column when I have a 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're only required to Group By columns that doesn't come with an aggregate function in the Select clause. So you can just use Group By ProductID and ProductName in this case.

您只需要在Select子句中将Group By列中没有聚合函数的列分组。因此,在这种情况下,您可以使用Group By 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

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