从一个表中选择多个列，但按一个分组 [英] 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

我试图同时具有OrderQuantity的总和来选择多个列和按产品ID分组.

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不唯一，如何选择多个列和按产品ID分组"?

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

这有效.

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