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