SQL Snippets ™: Tutorials for Oracle Developers

Consider a table like this one.

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

GRP_A      GRP_B             VAL
---------- ---------- ----------
a1         b1                 10
a1         b1                 20
a1         b2                 30
a1         b2                 40
a1         b2                 50
a2         b3                 12
a2         b3                 22
a2         b3                 32
 

GROUP BY allows us to group rows together so that we can include aggregate functions like COUNT, MAX, and SUM in the result set.

select grp_a, count(*), max( val ), sum( val )
from   t
GROUP BY GRP_A
order by grp_a ;
 

GRP_A        COUNT(*)   MAX(VAL)   SUM(VAL)
---------- ---------- ---------- ----------
a1                  5         50        150
a2                  3         32         66
 

We can specify multiple columns in the GROUP BY clause to produce a different set of groupings.

select grp_a, grp_b, count(*), max( val ), sum( val )
from   t
GROUP BY GRP_A, GRP_B
order by grp_a, grp_b ;
 

GRP_A      GRP_B        COUNT(*)   MAX(VAL)   SUM(VAL)
---------- ---------- ---------- ---------- ----------
a1         b1                  2         20         30
a1         b2                  3         50        120
a2         b3                  3         32         66
 

Parentheses may be added around the GROUP BY expression list. Doing so has no effect on the result.

select grp_a, grp_b, count(*), max( val ), sum( val )
from   t
GROUP BY ( GRP_A, GRP_B )
order by grp_a, grp_b ;
 

GRP_A      GRP_B        COUNT(*)   MAX(VAL)   SUM(VAL)
---------- ---------- ---------- ---------- ----------
a1         b1                  2         20         30
a1         b2                  3         50        120
a2         b3                  3         32         66
 

The GROUP BY expression list may be empty. This groups all rows retrieved by the query into a single group. Parentheses are mandatory when specifying an empty set.

select count(*), max( val ), sum( val )
from   t
GROUP BY () ;
 

  COUNT(*)   MAX(VAL)   SUM(VAL)
---------- ---------- ----------
         8         50        216
 

The last example is equivalent to specifying no GROUP BY clause at all, like this.

select count(*), max( val ), sum( val )
from   t ;
 

  COUNT(*)   MAX(VAL)   SUM(VAL)
---------- ---------- ----------
         8         50        216