SQL Snippets ™: Tutorials for Oracle Developers

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