Grouping Rows with GROUP BY

GROUP BY

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
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-13106.html]SQL Snippets: Grouping Rows with GROUP BY - GROUP BY[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-13106.html">SQL Snippets: Grouping Rows with GROUP BY - GROUP BY</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: Grouping Rows with GROUP BY - GROUP BY
  • URL (href): http://www.sqlsnippets.com/en/topic-13106.html