oracle rollup function with multiple columns

I find it rather easier to specify the exact sets I need with the GROUPING SET clause:

WITH data(val1, val2, val3) AS     ( SELECT 'a' ,'a-details' ,'1' FROM DUAL     UNION ALL     SELECT 'b' ,'b-details' ,'2' FROM DUAL     UNION ALL     SELECT 'c' ,'c-details' ,'3' FROM DUAL     )SELECT NVL(val1,'Total Result'),     val2,     SUM(val3) totfrom datagroup by grouping sets ((val1, val2),());

I suspect that it is more efficient, as it directly specifies the levels to calculate.

http://sqlfiddle.com/#!4/8301d/3

CUBE and ROLLUP are handy for generating large numbers of aggregation levels automatically (eg. every level in a dimensional hierarchy), and there might be a case for using GROUPING ID if you wanted to eliminate a small subset of levels from a large CUBE-generated set, but GROUPING SET is precisely designed for specifying particular aggregation levels.

GROUPING_ID Expression

You can use the GROUPING_ID expression to filter which levels of subtotals you need:

WITH data AS     ( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL     UNION ALL     SELECT 'b' ,'b-details' ,'2' FROM DUAL     UNION ALL     SELECT 'c' ,'c-details' ,'3' FROM DUAL     )SELECT NVL(val1,'Total Result'),     val2,     SUM(val3) totfrom datagroup by ROLLUP(val1, val2)HAVING GROUPING_ID(val1, val2) IN (0, 3);

Output:

NVL(VAL1,'TOTALRESULT') VAL2             TOT----------------------- --------- ----------a                       a-details          1 b                       b-details          2 c                       c-details          3 Total Result                               6 

GROUPING_ID returns 0 for rows with no subtotals, 1 for the first level and so on, we can take a look at values returned by it:

WITH data AS     ( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL     UNION ALL     SELECT 'b' ,'b-details' ,'2' FROM DUAL     UNION ALL     SELECT 'c' ,'c-details' ,'3' FROM DUAL     )SELECT NVL(val1,'Total Result'),     val2,     SUM(val3) tot,     GROUPING_ID(val1, val2) AS grp_idfrom datagroup by ROLLUP(val1, val2);

NVL(VAL1,'TOTALRESULT') VAL2             TOT     GRP_ID----------------------- --------- ---------- ----------a                       a-details          1          0 a                                          1          1 b                       b-details          2          0 b                                          2          1 c                       c-details          3          0 c                                          3          1 Total Result                               6          3 

Check at SQLFiddle

More about Rollup and related topics: Tim Hall about Rollup and Cube

(Edit)

The GROUPING Function

Regarding comment. You can use the GROUPING function:

GROUPING - accepts a single column as a parameter and returns "1" if the column contains a null value generated as part of a subtotal by a ROLLUP or CUBE operation or "0" for any other value, including stored null values.

Example of returned values:

WITH data AS     ( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL     UNION ALL     SELECT 'b' ,'b-details' ,'2' FROM DUAL     UNION ALL     SELECT 'c' ,'c-details' ,'3' FROM DUAL     )SELECT NVL(val1,'Total Result'),     val2,     SUM(val3) tot,     grouping(val1),     grouping(val2)from datagroup by ROLLUP(val1, val2);

Output:

NVL(VAL1,'TOTALRESULT') VAL2             TOT GROUPING(VAL1) GROUPING(VAL2)----------------------- --------- ---------- -------------- --------------a                       a-details          1              0              0 a                                          1              0              1 b                       b-details          2              0              0 b                                          2              0              1 c                       c-details          3              0              0 c                                          3              0              1 Total Result                               6              1              1

So your query should look like:

WITH data AS     ( SELECT 'a' AS val1 ,'a-details' AS val2 , '1' AS val3 FROM DUAL     UNION ALL     SELECT 'b' ,'b-details' ,'2' FROM DUAL     UNION ALL     SELECT 'c' ,'c-details' ,'3' FROM DUAL     )SELECT NVL(val1,'Total Result'),     val2,     SUM(val3) totfrom datagroup by ROLLUP(val1, val2)HAVING GROUPING(val1) = 1   OR (GROUPING(val1) + GROUPING(val2) = 0);

Output:

NVL(VAL1,'TOTALRESULT') VAL2             TOT----------------------- --------- ----------a                       a-details          1 b                       b-details          2 c                       c-details          3 Total Result                               6

Idea of using the GROUPING function from AskTom, here.