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
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.
Answer
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
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
ROLLUPorCUBEoperation 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.