sql server - Subtotals and SQL

Question

I have a SQL table of sales data (like the one below), how can I generate a result set that inlines the subtotals and idealy sorts the results in descending order of the highest paying customer?

So given a table like the following:

CUS_ID  | PRODUCT | AMOUNT
12      | A       |  2.50
12      | B       |  5.80
24      | A       |  10.00
24      | B       |  30.00

I would get the following result:

CUS_ID  | Product | AMOUNT
24      | A       |  10.00
24      | B       |  30.00
24      | Total   |  30.00
12      | A       |  2.50
12      | B       |  5.00
12      | Total   |  7.50

So far, I can come with the following query:

  SELECT cus_id, product, amount FROM Sales
UNION ALL
  (SELECT cus_id, 'ZZZZ' AS product, SUM(amount) FROM Sales GROUP BY cus_id)
ORDER BY cus_id, product

However, the query uses 'ZZZZ' instead of 'Total' (which can be fixed by find-and-replace afterwards), but it doesn't sort in order of amount.

EDIT: Please feel free to post answers that don't address sorting. Some of the answers were actually pretty helpful to me.

Answer 1

Have a look at something like

DECLARE @Sales TABLE(
        CUS_ID  INT,
        PRODUCT VARCHAR(20),
        AMOUNT FLOAT
)
INSERT INTO @Sales SELECT 12,'A', 2.50 
INSERT INTO @Sales SELECT 12,'B', 5.80 
INSERT INTO @Sales SELECT 24,'A', 10.00 
INSERT INTO @Sales SELECT 24,'B', 30.00

;WITH Vals AS (
        SELECT  cus_id, 
                product, 
                amount,
                1 DisplayOrder,
                SUM(amount) OVER(PARTITION BY cus_id) OrderTotal
        FROM    @Sales 
        UNION ALL   
        SELECT  cus_id, 
                'Total' AS product, 
                SUM(amount),
                2 DisplayOrder,
                SUM(amount)
        FROM    @Sales 
        GROUP BY cus_id
)
SELECT  cus_id,
        product,
        amount
FROM    Vals
ORDER BY  OrderTotal DESC,cus_id,DisplayOrder, product