oracle rollup function with multiple columns

Question

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
     SELECT 'c' ,'c-details' ,'3' FROM DUAL
     )
SELECT NVL(val1,'Total Result'),
     val2,
     SUM(val3) tot
from data
group by rollup(val1, val2);

I get an output like:

VAL1                             VAL2                                    TOT
-------------------------------- -------------------------------- ----------
a                                a-details                                 1 
a                                                                          1 
b                                b-details                                 2 
b                                                                          2 
c                                c-details                                 3 
c                                                                          3 
Total Result                                                               6 

But I need an output like:

VAL1                             VAL2                                    TOT
-------------------------------- -------------------------------- ----------
a                                a-details                                 1 
b                                b-details                                 2 
c                                c-details                                 3 
Total Result                                                               6 

Thanks in advance.

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
     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
from data
group 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.

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
     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
from data
group 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_id
from data
group 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 data
group 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) tot
from data
group 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.

Comments

[ajmalmhd04]

@Przemyslaw Kruglej, Please let me know anyway to use the query other than explicitly using (0,3) , as I need to change on adding columns.

[Przemyslaw Kruglej]

@ajmalmhd04 I think you should accept David Aldridge's answer. I had no idea you could do it like that with GORUPING SETS.

[ajmalmhd04]

Thanks @David Aldrige. So this is now a new learning topic for me.

[ajmalmhd04]

@PrzemyslawKruglej, Thanks for both your answers. Will learn more about GROUPING. You've anyhow +1 by me :).