SQL Snippets ™: Tutorials for Oracle Developers

We can use GROUP BY without specifying any aggregate functions in the SELECT list.

select grp_a, grp_b
from   t
GROUP BY GRP_A, GRP_B
order by grp_a, grp_b ;
 

GRP_A      GRP_B
---------- ----------
a1         b1
a1         b2
a2         b3
 

However, the same result is usually produced by specifying DISTINCT instead of using GROUP BY.

select DISTINCT grp_a, grp_b
from   t
order by grp_a, grp_b ;
 

GRP_A      GRP_B
---------- ----------
a1         b1
a1         b2
a2         b3
 

According to Tom Kyte the two approaches are effectively equivalent (see AskTom "DISTINCT VS, GROUP BY"). Queries that use DISTINCT are typically easier to understand, but the GROUP BY approach can provide an elegant solution to otherwise cumbersome queries when more than one set of groupings is required. For example, to produce a result set that is the union of:

• distinct values in GRP_A
• distinct values in GRP_B
• distinct values in GRP_A + GRP_B

the following query would be required if we used DISTINCT

select distinct grp_a, null as grp_b
from   t
union all
select distinct null, grp_b
from   t
union all
select distinct grp_a, grp_b
from   t
order by 1, 2 ;
 

GRP_A      GRP_B
---------- ----------
a1         b1
a1         b2
a1
a2         b3
a2
           b1
           b2
           b3
 

but a GROUP BY query could produce the same result with fewer lines of code.

select grp_a, grp_b
from   t
group by cube( grp_a, grp_b )
having grouping_id( grp_a, grp_b ) != 3
order by 1, 2 ;
 

GRP_A      GRP_B
---------- ----------
a1         b1
a1         b2
a1
a2         b3
a2
           b1
           b2
           b3
 

(We will learn about the CUBE and GROUPING_ID features later in this tutorial.)