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

Problem

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:

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

Answered by Urs Marian

Solution #2

Your Data

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

INSERT INTO @OrderDetails VALUES
(1001,'abc',5),(1002,'abc',23),(2002,'xyz',8),
(3004,'ytp',15),(4001,'aze',19),(1001,'abc',7)
``````

Query

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

Result

``````╔═══════════╦═════════════╦═══════╗
║ 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 https://dev.mysql.com/doc/refman/8.0/en/group-by-functions.html#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: https://stackoverflow.com/a/38779277/906265

Answered by Ivar

Solution #5

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

Please check if this works.

Answered by Vikram

Post is based on https://stackoverflow.com/questions/21217778/select-multiple-columns-from-a-table-but-group-by-one