Multi column oracle function

Question

I have 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 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 a conclusion like:

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

Thanks in advance.

Answer 1

It will be easier for me to specify the exact sets that 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 it is more efficient, as it directly determines the levels for calculation.

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

CUBE and ROLLUP are convenient for creating a large number of aggregation levels automatically (for example, each level in the size hierarchy), and there may be a case when using the GROUPING identifier if you want to exclude a small subset of levels from a large CUBE-generated set, but GROUPING SET is specifically designed to indicate certain levels of aggregation.

Answer 2

Expression GROUPING_ID

You can use the GROUPING_ID expression to filter the levels of intermediate results that 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 without subtotals, 1 for the first level, etc., we can look at the 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 for SQLFiddle

Learn more about Rollup and related topics: Tim Hall on folding and cube

( Edit )

GROUPING Function

Regarding the comment. You can use the GROUPING function:

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

Example return 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 this:

 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 

The idea of ​​using the GROUPING function from AskTom is here .