## Grouping Rows with GROUP BY

### GROUPING SETS, Composite Columns, and Empty Sets

#### Composite Columns

You can treat a collection of columns as an individual set by using parentheses in the GROUPING SETS clause. For example, to write a query that returns the equivalent of these two queries

```select grp_a, grp_b, count(*)
from   t
GROUP BY GRP_A, GRP_B
order by grp_a, grp_b ;
```
```GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a2         b3                  3
```
```select grp_a, null, count(*)
from   t
GROUP BY GRP_A
order by grp_a ;
```
```GRP_A      N   COUNT(*)
---------- - ----------
a1                    5
a2                    3
```

we could use the following GROUPING SETS clause.

```select grp_a, grp_b, count(*)
from   t
GROUP BY GROUPING SETS ( (GRP_A, GRP_B), GRP_A )
order by grp_a, grp_b ;
```
```GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a1                             5
a2         b3                  3
a2                             3
```

The term `(GRP_A, GRP_B)` is called a "composite column" when it appears inside a GROUPING SETS, ROLLUP, or CUBE clause.

#### Empty Sets

To add a grand total row to the result set an empty set, specified as (), can be used. In the example below the last row is generated by the empty set grouping.

```select grp_a, grp_b, count(*)
from   t
GROUP BY GROUPING SETS ( (GRP_A, GRP_B), () )
order by grp_a, grp_b ;
```
```GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a2         b3                  3
8
```

#### Gotcha - Parentheses without GROUPING SETS

Outside a GROUPING SETS clause (or ROLLUP or CUBE clauses) a parenthesized expression like `(GRP_A, GRP_B)` is no different than the same expression without parentheses. For example this query

```select grp_a, grp_b, count(*)
from   t
GROUP BY (GRP_A, GRP_B), GRP_A
order by grp_a, grp_b ;
```
```GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a2         b3                  3
```

returns the same results as this query

```select grp_a, grp_b, count(*)
from   t
GROUP BY GRP_A, GRP_B, GRP_A
order by grp_a, grp_b ;
```
```GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a2         b3                  3
```

which in turn has the same result set as this one.

```select grp_a, grp_b, count(*)
from   t
GROUP BY GRP_A, GRP_B
order by grp_a, grp_b ;
```
```GRP_A      GRP_B        COUNT(*)
---------- ---------- ----------
a1         b1                  2
a1         b2                  3
a2         b3                  3
```

#### 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-13122.html]SQL Snippets: Grouping Rows with GROUP BY - GROUPING SETS, Composite Columns, and Empty Sets[/url]