Coder Perfect

Choose numerous columns from a table, but group them together by one.


The table name is “OrderDetails” and columns are given below:

OrderDetailID || ProductID || ProductName || OrderQuantity

I’m trying to group numerous columns by ProductID while keeping the OrderQuantity SUM.

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

However, this code generates an error. I’ll have to add more column names to group by, but that’s not what I want, and because my data is so large, the results will be unexpected.

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

Since ProductName is not unique, how can I choose numerous columns and Group By ProductID column?

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

Asked by Ozan Ayten

Solution #1

When I have a multiple column selection, I use this approach to group by one column:

    MAX(intrare) AS intrare,
    MAX(iesire) AS iesire,
    MAX(intrare-iesire) AS stoc,
    MAX(data) AS data
FROM Produse

This works.

Answered by Urs Marian

Solution #2

Your Data

DECLARE @OrderDetails TABLE 
(ProductID INT,ProductName VARCHAR(10), OrderQuantity INT)



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


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

Answered by M.Ali

Solution #3

I simply wanted to add a more effective and generic solution to this type of issue. Working with sub queries is the main concept.

Make your group and join the same table based on the table’s ID.

Because your productId isn’t unique, your situation is more specific, and there are two possible solutions.

I’ll start with a more particular solution: Because your productId isn’t unique, we’ll need to take an extra step after grouping: select DISCTINCT product ids and run the sub query like this:

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 gives you exactly what you want.

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

However, there is a better method to go about it. I guess that ProductId is a foreign key to products table and i guess that there should be and OrderId primary key (unique) in this table.

In this scenario, there are only a few steps to take in order to include additional columns while only grouping on one. It will be the same solution as the previous one.

Consider the following t Value table:

If I want to group by description while also displaying all columns, I’ll use this method.

All I need to do now is:

and that’s it!

The question is as follows:

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’s what we came up with:

Answered by Haithem KAROUI

Solution #4

The mysql GROUP CONCAT function group-concat could be useful.

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 to @Urs Marian’s suggestion here:

Answered by Ivar

Solution #5

      SELECT ProductID, Sum(OrderQuantity) AS TotalOrderQuantity 
      FROM OrderDetails GROUP BY ProductID
    SELECT DISTINCT OrderDetails.ProductID, OrderDetails.ProductName, OrderDetails.OrderQuantity,CTE_SUM.TotalOrderQuantity 
    OrderDetails INNER JOIN CTE_SUM 
    ON OrderDetails.ProductID = CTE_SUM.ProductID

Please check if this works.

Answered by Vikram

Post is based on