How to add total sum of per Customer under each ID

Question

I have a demo table like this:

OrderID CustomerID ProductID OrderAmount OrderDate

1 101 201 100.00 2024-08-01 00:00:00.000

2 101 202 150.00 2024-08-03 00:00:00.000

3 102 201 200.00 2024-08-10 00:00:00.000

4 101 203 120.00 2024-09-15 00:00:00.000

5 103 202 180.00 2024-10-01 00:00:00.000

6 102 202 160.00 2024-09-20 00:00:00.000

7 101 201 110.00 2024-08-05 00:00:00.000

8 103 201 140.00 2024-09-25 00:00:00.000

9 102 203 130.00 2024-09-28 00:00:00.000

10 103 202 220.00 2024-10-05 00:00:00.000

Have wrote a query as below, and need to add total sum result of each customer.

SELECT CustomerID, ProductID,SUM(OrderAmount) AS TotalAmount
FROM Orders
GROUP BY CustomerID,ProductID
ORDER BY CustomerID

The result should be like this:

CustomerID ProductID TotalAmount

101 201 210.00

101 202 150.00

101 203 120.00

101 Total xxx.00

102 201 200.00

102 202 160.00

102 203 130.00

102 Total xxx.00

103 201 140.00

103 202 400.00

103 Total xxx.00

How to achieve this output?

Answer 1

Hi @Zhu

You may consider using Grouping Sets like this:

SELECT CustomerID
      ,ISNULL(CAST(ProductID AS VARCHAR(10)),'Total') AS ProductID
	  ,SUM(OrderAmount) AS TotalAmount
FROM Orders
GROUP BY GROUPING SETS ((CustomerID,ProductID)
                       ,(CustomerID))

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".