Cursor? Loop? Aggregate up rows data along with row results

Question

I have the following table & data

Table name = MyTable

Description | Partition     | Total
------------|---------------|--------------
CASH        | Reconciled    | 25
CASH        | Adjustm       | 50
CASH        | Balanc        | 120
LOANS       | Adjustm       | 44
LOANS       | Balanc        | 32
CARDS       | Adjustm       | 81
CARDS       | Balanc        | 67
MTG         | Adjustm       | 14
MTG         | Balanc        | 92

The requirement is simple enough - it's a straight select from the table, but for each unique description, I need to sum up the totals of all the partitions, such that the user will see

Description | Partition     | Total
------------|---------------|--------------
CASH        | TOTAL         | 195   <
CASH        | Reconciled    | 25
CASH        | Adjustm       | 50
CASH        | Balanc        | 120
LOANS       | TOTAL         | 76    <
LOANS       | Adjustm       | 44
LOANS       | Balanc        | 32
CARDS       | TOTAL         | 148   <
CARDS       | Adjustm       | 81
CARDS       | Balanc        | 67
MTG         | TOTAL         | 106   <
MTG         | Adjustm       | 14
MTG         | Balanc        | 92

It's a stored proc I'm writing - I don't have the option of pulling this into a MT to perform this so I need to perform it in the body of the stored proc. Am I looking at some while Loop or Cursor to provide the roll up I need, or is there another glaringly obvious and easy solution that I'm just not seeing? Aside from the roll up, it's a straight

select * from MyTable

DB is Sybase.

Thanks

Answer 1

You can do this by using the GROUPING SETS extension of the GROUP BY clause:

SELECT  Description, 
        COALESCE(Parition, 'Total') AS Partition,
        SUM(Total) AS Total
FROM    MyTable
GROUP BY GROUPING SETS ((Description, Partition), (Description));

or you could use:

SELECT  Description, 
        COALESCE(Parition, 'Total') AS Partition,
        SUM(Total) AS Total
FROM    MyTable
GROUP BY ROLLUP (Description, Partition);

Without ROLLUP, you can do this using UNION ALL:

SELECT  Description, 
        Parition,
        Total
FROM    MyTable
UNION ALL
SELECT  Description, 
        'Total' AS Partition,
        SUM(Total) AS Total
FROM    MyTable
GROUP BY Description;