## 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]