Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.6k views
in Technique[技术] by (71.8m points)

sql - Select multiple columns from a table, but group by one

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

OrderDetailID || ProductID || ProductName || 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.

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

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

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

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

This works.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...