Oracle Rollup Function With Multiple Columns

I've a simple query: WITH data(val1, val2, val3) AS ( SELECT 'a' ,'a-details' ,'1' FROM DUAL UNION ALL SELECT 'b' ,'b-details' ,'2' FROM DUAL UNION ALL SEL

Solution 1:

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
     UNIONALLSELECT'b' ,'b-details' ,'2'FROM DUAL
     UNIONALLSELECT'c' ,'c-details' ,'3'FROM DUAL
     )
SELECT NVL(val1,'Total Result'),
     val2,
     SUM(val3) tot
from data
groupbygrouping 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.

Solution 2:

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
     UNIONALLSELECT'b' ,'b-details' ,'2'FROM DUAL
     UNIONALLSELECT'c' ,'c-details' ,'3'FROM DUAL
     )
SELECT NVL(val1,'Total Result'),
     val2,
     SUM(val3) tot
from data
groupbyROLLUP(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
     UNIONALLSELECT'b' ,'b-details' ,'2'FROM DUAL
     UNIONALLSELECT'c' ,'c-details' ,'3'FROM DUAL
     )
SELECT NVL(val1,'Total Result'),
     val2,
     SUM(val3) tot,
     GROUPING_ID(val1, val2) AS grp_id
from data
groupbyROLLUP(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
     UNIONALLSELECT'b' ,'b-details' ,'2'FROM DUAL
     UNIONALLSELECT'c' ,'c-details' ,'3'FROM DUAL
     )
SELECT NVL(val1,'Total Result'),
     val2,
     SUM(val3) tot,
     grouping(val1),
     grouping(val2)
from data
groupbyROLLUP(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
     UNIONALLSELECT'b' ,'b-details' ,'2'FROM DUAL
     UNIONALLSELECT'c' ,'c-details' ,'3'FROM DUAL
     )
SELECT NVL(val1,'Total Result'),
     val2,
     SUM(val3) tot
from data
groupbyROLLUP(val1, val2)
HAVINGGROUPING(val1) =1OR (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.