从表格中选择多个列，但按组合一 [英] Select multiple columns from table but Group By one

问题描述

表名是OrderDetails，列在下面给出

OrderDetailID || ProductID || ProductName || OrderQuantity

我试图实现的是，选择多个列和Group By ProductID，同时具有OrderQuantity的SUM。

 选择ProductID，ProductName，OrderQuantity Sum（OrderQuantity）
从OrderDetails Group按产品ID 
  

但是当然这段代码给出了一个错误，我不得不添加其他列名来分组，但那不是我想要的，因为我的数据有很多项目，所以结果是意想不到的。

 示例数据查询：ProductID，ProductName， OrderQuantity from OrderDetails 
结果如下：
 
 ProductID ProductName OrderQuantity 
 1001 abc 5 
 1002 abc 23（ProductNames可以相同）
 2002 xyz 8 
 3004 ytp 15 
 4001 aze 19 
 1001 abc 7（同一产品ID的第二行）
  

预期结果：

  ProductID ProductName OrderQuantity 
 1001 abc 12（按产品ID分组加算）
 1002 abc 23 
 2002 xyz 8 
 3004 ytp 15 
 4001 aze 19 
  
 
 
 
如果需要，我可以提供更多详细信息，问题简述：由于ProductName不是唯一的，因此选择多个列和Group By ProductID列。虽然这样做也有OrderQuantity列的总和。 
解决方案
 当我有多列选择时，我使用这个技巧来按一列进行分组：
 
 
  SELECT MAX（id）AS id，
 Nume，
 MAX（intrare）AS intrare，
 MAX（iesire）AS iesire，
 MAX（intra-iesire）AS stoc，
 MAX（data）AS data 
 FROM Produse 
 GROUP BY Nume 
 ORDER BY Nume 
  
这是可行的。
 
Table name is OrderDetails and columns are given below

OrderDetailID || ProductID || ProductName || OrderQuantity 

What I'm trying to achieve is, selecting 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 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. 
解决方案
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.

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