## Grouping Rows with GROUP BY

### Concatenated Groupings

The last type of grouping shortcut we will examine is called a Concatenated Grouping. With it one can re-write a query like this one, which effectively performs a cross-product of GRP_A with GRP_B and GRP_C,

```select
grp_a
, grp_b
, grp_c
, count(*)
from
t
group by
grouping sets
(
( grp_a, grp_b )
, ( grp_a, grp_c )
)
order by
1, 2, 3
;
```
```GRP_A      GRP_B      GRP_C        COUNT(*)
---------- ---------- ---------- ----------
a1         b1                             2
a1         b2                             3
a1                    c1                  4
a1                    c2                  1
a2         b3                             3
a2                    c2                  3
```

into one like this.

```set null '(null)'

select
grp_a
, grp_b
, grp_c
, count(*)
from
t
group by
grp_a
, grouping sets( grp_b, grp_c )
order by
1, 2, 3
;
```
```GRP_A      GRP_B      GRP_C        COUNT(*)
---------- ---------- ---------- ----------
a1         b1         (null)              2
a1         b2         (null)              3
a1         (null)     c1                  4
a1         (null)     c2                  1
a2         b3         (null)              3
a2         (null)     c2                  3
```

The cross-product effect is more apparent when a query like this one

```select
grp_a
, grp_b
, grp_c
, count(*)
from
t
group by
grouping sets
(
( grp_a, grp_c )
, ( grp_a, grp_d )
, ( grp_b, grp_c )
, ( grp_b, grp_d )
)
order by
1, 2, 3
;
```
```GRP_A      GRP_B      GRP_C        COUNT(*)
---------- ---------- ---------- ----------
a1         (null)     c1                  4
a1         (null)     c2                  1
a1         (null)     (null)              5
a2         (null)     c2                  3
a2         (null)     (null)              3
(null)     b1         c1                  2
(null)     b1         (null)              2
(null)     b2         c1                  2
(null)     b2         c2                  1
(null)     b2         (null)              3
(null)     b3         c2                  3
(null)     b3         (null)              3
```

is re-written into one like this.

```select
grp_a
, grp_b
, grp_c
, count(*)
from
t
group by
grouping sets( grp_a, grp_b )
, grouping sets( grp_c, grp_d )
order by
1, 2, 3
;
```
```GRP_A      GRP_B      GRP_C        COUNT(*)
---------- ---------- ---------- ----------
a1         (null)     c1                  4
a1         (null)     c2                  1
a1         (null)     (null)              5
a2         (null)     c2                  3
a2         (null)     (null)              3
(null)     b1         c1                  2
(null)     b1         (null)              2
(null)     b2         c1                  2
(null)     b2         c2                  1
(null)     b2         (null)              3
(null)     b3         c2                  3
(null)     b3         (null)              3
```

Personally I have never found the need to use concatenated groupings. I find that specifically listing the desired groupings in a single GROUPING SETS clause or using a single ROLLUP or CUBE operator makes my queries easier to understand and debug. Concatenated groupings can, however, prove useful in data warehouse queries that deal with hierarchical cubes of data. See Concatenated Groupings for more information.

#### 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-13132.html]SQL Snippets: Grouping Rows with GROUP BY - Concatenated Groupings[/url]