Oracle Summary Query GROUPING_ID - CUBE
Summary Query - CUBE 
SELECT
 q_t.idx,
 GROUPING_ID(q_t.idx) grp_id,
 COUNT(*) counts,
 SUM(q_t.val) sum_val
FROM
 (SELECT 1 idx, 100 val FROM DUAL  UNION
  SELECT 1 idx, 101 val FROM DUAL  UNION
  SELECT 2 idx, 201 val FROM DUAL  UNION
  SELECT 2 idx, 202 val FROM DUAL  UNION
  SELECT 3 idx, 301 val FROM DUAL  UNION
  SELECT 3 idx, 302 val FROM DUAL
 ) q_t
GROUP BY 
  CUBE(q_t.idx);
Grouping_ID Summary Query - CUBE
Oracle Summary Query To Compute Total Using GROUPING - CUBE
Summary Query Compute Totals - CUBE 
SELECT
  DECODE(GROUPING(q_t.idx),
   0, TO_CHAR(q_t.idx),
   'Total ...') Group_by_cube,
 GROUPING_ID(q_t.idx) grp_id,
 COUNT(*) counts,
 SUM(q_t.val) sum_val
FROM
 (SELECT 1 idx, 100 val FROM DUAL  UNION
  SELECT 1 idx, 101 val FROM DUAL  UNION
  SELECT 2 idx, 201 val FROM DUAL  UNION
  SELECT 2 idx, 202 val FROM DUAL  UNION
  SELECT 3 idx, 301 val FROM DUAL  UNION
  SELECT 3 idx, 302 val FROM DUAL
 ) q_t
GROUP BY
  CUBE(q_t.idx)
ORDER BY 1;
Grouping_ID Summary Query Compute Totals - CUBE
Note: The total value is displayed as the last row by using ORDER BY. If the grouped data is non-numeric, GROUPING function along with ORDER BY can be used to display the total as the last row as shown below. However the order is still not in A to Z order (can be solved by ROLLUP without ORDER BY).

Summary Query To Compute Totals - CUBE 
SELECT
 DECODE(GROUPING(q_t.idx),
  0, q_t.idx,
  'Total ...') Group_by_cube,
 GROUPING_ID(q_t.idx) grp_id,
 COUNT(*) counts,
 SUM(q_t.val) sum_val
FROM
 (SELECT 'AA' idx, 100 val FROM DUAL  UNION
  SELECT 'AA' idx, 101 val FROM DUAL  UNION
  SELECT 'XX' idx, 201 val FROM DUAL  UNION
  SELECT 'XX' idx, 202 val FROM DUAL  UNION
  SELECT 'ZZ' idx, 301 val FROM DUAL  UNION
  SELECT 'ZZ' idx, 302 val FROM DUAL
 ) q_t
GROUP BY 
  CUBE(q_t.idx)
ORDER BY
 DECODE(GROUPING(q_t.idx), 0, 1);
Grouping_ID Summary Query Compute Totals - CUBE
Oracle registered trademark of Oracle Corporation.
Last Revised on: February 23, 2012

  73953