Oracle GROUP_ID
-- Query with GROUP_ID showing duplicate
-- GROUP BY ROLLUP data 
-- GROUP BY ROLLUP(idx), idx, dept_id
-- with GROUP_ID value of 1 
-- (rows 4,5,6 in query output [1] shown 
-- beside)
WITH q_tab AS
 (SELECT 1 idx, 100 dept_id, 1000 sales FROM DUAL 
 UNION ALL
  SELECT 1 idx, 100 dept_id, 1100 sales FROM DUAL
 UNION ALL
  SELECT 1 idx, 100 dept_id, 1000 sales FROM DUAL
 UNION ALL
  SELECT 2 idx, 102 dept_id, 1050 sales FROM DUAL
 UNION ALL
  SELECT 2 idx, 102 dept_id, 1050 sales FROM DUAL
 UNION ALL
  SELECT 3 idx, 103 dept_id, 1010 sales FROM DUAL
 )
SELECT 
  idx,  dept_id, COUNT(*) counts,
  SUM(sales) sum_sales,
  GROUP_ID() group_id
FROM q_tab
GROUP BY ROLLUP(idx), idx, dept_id;
  Query Output [1]
Group_ID with value of 1 in rows 4,5,6


To analyze duplicate data, Oracle analytic function ROW_NUMBER() OVER (PARTITION BY .. ORDER BY ..) would be the preferred method.

GROUP_ID With ROLLUP on Grouped Columns
-- By GROUP BY with ROLLUP on idx, dept_id,
-- the GROUP_ID value is 0 for all rows
-- (as shown in query output [2] beside)

WITH q_tab AS
 (SELECT 1 idx, 100 dept_id, 1000 sales FROM DUAL 
 UNION ALL
  SELECT 1 idx, 100 dept_id, 1100 sales FROM DUAL
 UNION ALL
  SELECT 1 idx, 100 dept_id, 1000 sales FROM DUAL
 UNION ALL
  SELECT 2 idx, 102 dept_id, 1050 sales FROM DUAL
 UNION ALL
  SELECT 2 idx, 102 dept_id, 1050 sales FROM DUAL
 UNION ALL
  SELECT 3 idx, 103 dept_id, 1010 sales FROM DUAL
 )
SELECT 
  idx,  dept_id, COUNT(*) counts,
  SUM(sales) sum_sales,
  GROUP_ID() group_id
FROM q_tab
GROUP BY ROLLUP(idx, dept_id);
  Query Output [2]
All Group_ID with value of 0


Oracle GROUPING_ID, Grouping Sets

Oracle registered trademark of Oracle Corporation.

Last Revised on: March 01, 2012

  1335