Aishwarya Shiva Aishwarya Shiva - 6 months ago 11
SQL Question

Combine multiple rows by summing up some of its columns

I have following data in my table:

+-------------+---------------+--------+----------+------------+----------+---------+-------------------------+------------+-----------+--+
| ProductName | PricePerPiece | CafeID | Quantity | TotalPrice | MemberID | OrderID | OrderDate | OrderTotal | ProductID | |
+-------------+---------------+--------+----------+------------+----------+---------+-------------------------+------------+-----------+--+
| Coffee | 10 | 1 | 1 | 10 | 20 | 1008 | 2016-06-01 02:38:47.113 | 196 | 1 | |
| Mocha | 152 | 1 | 1 | 152 | 20 | 1008 | 2016-06-01 02:38:47.113 | 196 | 4 | |
| Pikachu | 34 | 1 | 1 | 34 | 20 | 1008 | 2016-06-01 02:38:47.113 | 196 | 5 | |
| Coffee | 10 | 1 | 1 | 10 | 20 | 1009 | 2016-06-01 02:39:49.750 | 35 | 1 | |
| Cappucino | 25 | 1 | 1 | 25 | 20 | 1009 | 2016-06-01 02:39:49.750 | 35 | 3 | |
+-------------+---------------+--------+----------+------------+----------+---------+-------------------------+------------+-----------+--+


I want a single row for a particular product in which its
Quantity
and
TotalPrice
are added together, giving following expected output:

+-------------+---------------+--------+----------+------------+----------+---------+-------------------------+------------+-----------+--+
| ProductName | PricePerPiece | CafeID | Quantity | TotalPrice | MemberID | OrderID | OrderDate | OrderTotal | ProductID | |
+-------------+---------------+--------+----------+------------+----------+---------+-------------------------+------------+-----------+--+
| Coffee | 10 | 1 | 2 | 20 | 20 | 1008 | 2016-06-01 02:38:47.113 | 196 | 1 | |
| Mocha | 152 | 1 | 1 | 152 | 20 | 1008 | 2016-06-01 02:38:47.113 | 196 | 4 | |
| Pikachu | 34 | 1 | 1 | 34 | 20 | 1008 | 2016-06-01 02:38:47.113 | 196 | 5 | |
| Cappucino | 25 | 1 | 1 | 25 | 20 | 1009 | 2016-06-01 02:39:49.750 | 35 | 3 | |
+-------------+---------------+--------+----------+------------+----------+---------+-------------------------+------------+-----------+--+


I tried following query:

SELECT [ProductName], [PricePerPiece], [CafeID],
SUM([Quantity]) AS [Quantity], SUM([TotalPrice]) AS [TotalPrice],
[MemberID],[OrderID],[OrderDate],[OrderTotal],[ProductID]
FROM [ProductOrders] GROUP BY [ProductID], [ProductName], [PricePerPiece], [CafeID], [MemberID], [OrderID], [OrderDate], [OrderTotal]


But its giving the same result just showing
Coffee
rows together but not adding up its
Quantity
and
TotalPrice
. Please tell me how to do it?

Answer

OrderTotal has a different value for each of the Coffee rows. You can choose to not select OrderTotal, or you can SUM(OrderTotal) as well.

Any distinct values that you select but aren't summing (or otherwise aggregating) will not group together.

On closer look: Same goes for OrderDate and OrderID. Based on your expected output, you want to select MIN(OrderID), MIN(OrderDate), MAX(OrderTotal)

Comments