Unlike a regular GROUP BY clause, including the same column more than once in a GROUPING SETS clause produces duplicate rows.
select grp_a, count(*) from t GROUP BY GROUPING SETS ( GRP_A, GRP_A ) order by grp_a ;
GRP_A COUNT(*) ---------- ---------- a1 5 a1 5 a2 3 a2 3
select grp_a, count(*) from t GROUP BY GROUPING SETS ( GRP_A, GRP_A, GRP_A ) order by grp_a ;
GRP_A COUNT(*) ---------- ---------- a1 5 a1 5 a1 5 a2 3 a2 3 a2 3
The GROUP_ID function can be used to distinguish duplicates from each other.
select grp_a, count(*), GROUP_ID() from t GROUP BY GROUPING SETS ( GRP_A, GRP_A, GRP_A ) order by grp_a, group_id() ;
GRP_A COUNT(*) GROUP_ID() ---------- ---------- ---------- a1 5 0 a1 5 1 a1 5 2 a2 3 0 a2 3 1 a2 3 2
In the trivial example above it seems there would be little practical use for GROUP_ID. There are times when more complex GROUP BY clauses can return duplicate rows however. It is in such queries that GROUP_ID proves useful.
Note that GROUP_ID will always be 0 in a result set that contains no duplicates.
select grp_a, grp_b, count(*), GROUP_ID() from t GROUP BY GROUPING SETS ( GRP_A, GRP_B ) order by grp_a, grp_b ;
GRP_A GRP_B COUNT(*) GROUP_ID() ---------- ---------- ---------- ---------- a1 5 0 a2 3 0 b1 2 0 b2 3 0 b3 3 0