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

Select multiple columns from a table, but group by one

提问by Ozan Ayten

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.

我正在尝试选择多个列和按 ProductID 分组，同时具有 OrderQuantity 的总和。

 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?

由于 ProductName 不是唯一的，如何选择多个列和 Group By ProductID 列？

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

在执行此操作时，还要获取 OrderQuantity 列的总和。

回答by Urs Marian

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.

这有效。

回答by M.Ali

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),
(3004,'ytp',15),(4001,'aze',19),(1001,'abc',7)

Query

询问

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

Result

结果

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

回答by har07

You can try this:

你可以试试这个：

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

You're only required to Group Bycolumns that doesn't come with an aggregate function in the Selectclause. So you can just use Group ByProductID and ProductName in this case.

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

回答by Haithem KAROUI

I just wanted to add a more effective and generic way to solve this kind of problems. The main idea is about working with sub queries.

我只是想添加一种更有效和通用的方法来解决此类问题。主要思想是关于使用子查询。

do your group by and join the same table on the ID of the table.

做你的分组，并在表的 ID 上加入同一个表。

your case is more specific since your productId is not uniqueso there is 2 ways to solve this.

您的情况更具体，因为您的 productId不是唯一的，因此有两种方法可以解决此问题。

I will begin by the more specific solution: Since your productId is not uniquewe will need an extra step which is to select DISCTINCTproduct ids after grouping and doing the sub query like following:

我将从更具体的解决方案开始：由于您的 productId不是唯一的，我们将需要一个额外的步骤，即DISCTINCT在分组和执行子查询后选择产品 ID，如下所示：

WITH CTE_TEST AS (SELECT productId, SUM(OrderQuantity) Total
                    FROM OrderDetails
                    GROUP BY productId)
SELECT DISTINCT(OrderDetails.ProductID), OrderDetails.ProductName, CTE_TEST.Total
FROM OrderDetails 
INNER JOIN CTE_TEST ON CTE_TEST.ProductID = OrderDetails.ProductID

this returns exactly what is expected

这完全返回了预期的结果

 ProductID     ProductName         Total
    1001          abc               12    
    1002          abc               23
    2002          xyz               8
    3004          ytp               15
    4001          aze               19

Butthere a cleaner way to do this. I guess that ProductIdis a foreign key to products table and i guess that there should be and OrderIdprimary key(unique) in this table.

但是有一个更干净的方法来做到这一点。我猜这ProductId是 products 表的外键，我猜这个表中应该有OrderId主键（唯一）。

in this case there are few steps to do to include extra columns while grouping on only one. It will be the same solution as following

在这种情况下，只需执行几个步骤即可包含额外的列，同时仅对一个进行分组。这将是与以下相同的解决方案

Let's take this t_Valuetable for example:

我们以这张t_Value表为例：

If i want to group by description and also display all columns.

如果我想按描述分组并显示所有列。

All i have to do is:

我所要做的就是：

1. create WITH CTE_Namesubquery with your GroupBy column and COUNT condition
2. select all(or whatever you want to display) from value table and the total from the CTE
3. INNER JOINwith CTE on the ID(primary key or unique constraint) column

1. WITH CTE_Name使用您的 GroupBy 列和 COUNT 条件创建子查询
2. 从值表中选择所有（或您想显示的任何内容）和 CTE 中的总数
3. INNER JOIN在 ID（主键或唯一约束）列上使用 CTE

and that's it!

就是这样！

Here is the query

这是查询

WITH CTE_TEST AS (SELECT Description, MAX(Id) specID, COUNT(Description) quantity 
                    FROM sch_dta.t_value
                    GROUP BY Description)
SELECT sch_dta.t_Value.*, CTE_TEST.quantity 
FROM sch_dta.t_Value 
INNER JOIN CTE_TEST ON CTE_TEST.specID = sch_dta.t_Value.Id

And here is the result:

结果如下：

Hope this helps!

希望这可以帮助！

HK

香港

回答by Vikram

    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.

请检查这是否有效。

回答by A_B

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

回答by Joe_DM

==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 Byor excluded from your results.

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

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

例如，如果您Group By只有 ProductID，SQL 如何将这些结果呈现给您？

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

回答by Aivaras

mysqlGROUP_CONCATfunction could help https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat

mysqlGROUP_CONCAT函数可以帮助https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#function_group-concat

SELECT ProductID, GROUP_CONCAT(DISTINCT ProductName) as Names, SUM(OrderQuantity)
FROM OrderDetails GROUP BY ProductID

This would return:

这将返回：

ProductID     Names          OrderQuantity
1001          red            5
1002          red,black      6
1003          orange         8
1004          black,orange   15

Similar idea as the one @Urs Marian here posted https://stackoverflow.com/a/38779277/906265

与这里发布的@Urs Marian 类似的想法https://stackoverflow.com/a/38779277/906265

回答by Jessica Pennell

In my opinion this is a serious language flaw that puts SQL light years behind other languages. This is my incredibly hacky workaround. It is a total kludge but it always works.

在我看来，这是一个严重的语言缺陷，它使 SQL 落后于其他语言数光年。这是我难以置信的 hacky 解决方法。这完全是一团糟，但它总是有效。

Before I do I want to draw attention to @Peter Mortensen's answer, which in my opinion is the correct answer. The only reason I do the below instead is because most implementations of SQL have incredibly slow join operations and force you to break "don't repeat yourself". I need my queries to populate fast.

在此之前，我想提请注意@Peter Mortensen 的回答，我认为这是正确的答案。我这样做的唯一原因是因为大多数 SQL 实现的连接操作速度非常慢，并迫使您打破“不要重复自己”。我需要我的查询快速填充。

Also this is an old way of doing things. STRING_AGGand STRING_SPLITare a lot cleaner. Again I do it this way because it always works.

这也是一种古老的做事方式。STRING_AGG和STRING_SPLIT更干净。我再次这样做，因为它总是有效。

-- remember Substring is 1 indexed, not 0 indexed
SELECT ProductId
  , SUBSTRING (
      MAX(enc.pnameANDoq), 1, CHARINDEX(';', MAX(enc.pnameANDoq)) - 1
    ) AS ProductName
  , SUM ( CAST ( SUBSTRING (
      MAX(enc.pnameAndoq), CHARINDEX(';', MAX(enc.pnameANDoq)) + 1, 9999
    ) AS INT ) ) AS OrderQuantity
FROM (
    SELECT CONCAT (ProductName, ';', CAST(OrderQuantity AS VARCHAR(10)))
      AS pnameANDoq, ProductID
    FROM OrderDetails
  ) enc
GROUP BY ProductId

Or in plain language :

或者用简单的语言：

• Glue everything except one field together into a string with a delimeter you know won't be used
• Use substring to extract the data after it's grouped

• 将除一个字段之外的所有内容粘合成一个带有您知道不会使用的分隔符的字符串
• 分组后使用子字符串提取数据

Performance wise I have always had superior performance using strings over things like, say, bigints. At least with microsoft and oracle substring is a fast operation.

性能方面，我一直使用字符串比 bigint 之类的东西具有更好的性能。至少用microsoft和oracle substring是一个快速的操作。

This avoids the problems you run into when you use MAX() where when you use MAX() on multiple fields they no longer agree and come from different rows. In this case your data is guaranteed to be glued together exactly the way you asked it to be.

这避免了您在使用 MAX() 时遇到的问题，当您在多个字段上使用 MAX() 时，它们不再一致并且来自不同的行。在这种情况下，您的数据可以保证完全按照您要求的方式粘合在一起。

To access a 3rd or 4th field, you'll need nested substrings, "after the first semicolon look for a 2nd". This is why STRING_SPLIT is better if it is available.

要访问第三个或第四个字段，您需要嵌套子字符串，“在第一个分号之后查找第二个”。这就是为什么 STRING_SPLIT 在可用时更好的原因。

Note : While outside the scope of your question this is especially useful when you are in the opposite situation and you're grouping on a combined key, but don't want every possible permutation displayed, that is you want to expose 'foo' and 'bar' as a combined key but want to group by 'foo'

注意：虽然超出了您的问题范围，但当您处于相反的情况并且您在组合键上分组时，这尤其有用，但不希望显示所有可能的排列，即您想要公开 'foo' 和'bar' 作为组合键，但想按 'foo' 分组

回答by mrfournier

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中的一个类似问题。