从表中选择多列，但按一个分组 [英] Select multiple columns from a table, but group by one

问题描述

表名为OrderDetails"，列如下:

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

OrderDetailID || ProductID || ProductName || OrderQuantity

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

I'm trying to select multiple columns and Group By ProductID while having SUM of 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.

样本数据查询:

来自 OrderDetails 的 ProductID、ProductName、OrderQuantity

ProductID,ProductName,OrderQuantity from OrderDetails

结果如下:

 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)

预期结果:

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

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

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

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

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

推荐答案

当我有多个列选择时，我使用这个技巧按一列分组:

I use this trick 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

这行得通.

这篇关于从表中选择多列，但按一个分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！