## Grouping Rows with GROUP BY

### GROUPING_ID

In the preceding topic we saw how the GROUPING function could be used to identify null values representing the set of all values produced by a GROUPING SETS, ROLLUP, or CUBE operation. What if we wanted to distinguish entire rows from each other? We could use a number of different GROUPING() calls like this

```column bit_vector format a10

select
TO_CHAR( GROUPING( GRP_A ) )
|| TO_CHAR( GROUPING( GRP_B ) )
AS BIT_VECTOR
,
DECODE
( TO_CHAR( GROUPING( GRP_A ) )
|| TO_CHAR( GROUPING( GRP_B ) )
, '01', 'Group "' || GRP_A || '" Total'
, '10', 'Group "' || GRP_B || '" Total'
, '11', 'Grand Total'
, NULL
) AS LABEL
,
count(*)
from
t2
group by
grouping sets ( grp_a, grp_b, ()  )
order by
GROUPING( GRP_A )
, grp_a
, GROUPING( GRP_B )
, grp_b
;
```
```BIT_VECTOR LABEL                      COUNT(*)
---------- ------------------------ ----------
01         Group "A1" Total                  5
01         Group "A2" Total                  1
10         Group "X1" Total                  1
10         Group "X2" Total                  1
10         Group "" Total                    4
11         Grand Total                       6
```

but if the number of grouping sets were large concatenating all the required GROUPING() terms together would get cumbersome. Fortunately for us the GROUPING_ID function exists. It yields the decimal value of a bit vector (a string of zeros and ones) formed by concatenating all the GROUPING values for its parameters. The following example shows how it works.

```select
to_char( grouping( grp_a ) )
|| to_char( grouping( grp_b ) )
as bit_vector                   -- this column is only included for clarity
, GROUPING_ID( GRP_A, GRP_B )
, grp_a
, grp_b
, count(*)
from
t2
group by
grouping sets ( grp_a, grp_b, () )
order by
GROUPING_ID( GRP_A, GRP_B )
, grp_a
, grp_b
;
```
```BIT_VECTOR GROUPING_ID(GRP_A,GRP_B) GRP_A      GRP_B        COUNT(*)
---------- ------------------------ ---------- ---------- ----------
01                                1 A1                             5
01                                1 A2                             1
10                                2            X1                  1
10                                2            X2                  1
10                                2                                4
11                                3                                6
```

Here is how we could use GROUPING_ID to streamline our original query.

```select
DECODE
( GROUPING_ID( GRP_A, GRP_B )
, 1, 'Group "' || GRP_A || '" Total'
, 2, 'Group "' || GRP_B || '" Total'
, 3, 'Grand Total'
, NULL
) AS LABEL
, count(*)
from
t2
group by
grouping sets ( grp_a, grp_b, ()  )
order by
GROUPING_ID( GRP_A, GRP_B )
, grp_a
, grp_b
;
```
```LABEL                      COUNT(*)
------------------------ ----------
Group "A1" Total                  5
Group "A2" Total                  1
Group "X1" Total                  1
Group "X2" Total                  1
Group "" Total                    4
Grand Total                       6
```

#### Composite Columns

The following example shows how GROUPING_ID works when a composite column, (GRP_A, GRP_B), is included in the GROUPING SETS clause.

```select
GROUPING_ID( GRP_A, GRP_B )
, grp_a
, grp_b
, count(*)
from
t2
group by
grouping sets ( (grp_a, grp_b), grp_a, grp_b, ()  )
order by
1
, 2
, 3
;
```
```GROUPING_ID(GRP_A,GRP_B) GRP_A      GRP_B        COUNT(*)
------------------------ ---------- ---------- ----------
0 A1         X1                  1
0 A1         X2                  1
0 A1                             3
0 A2                             1
1 A1                             5
1 A2                             1
2            X1                  1
2            X2                  1
2                                4
3                                6
```

#### GROUPING_ID and HAVING

GROUPING_ID can also be used in the HAVING clause to filter out unwanted groupings. Say, for example, we started with a query like this one

```select
grouping_id( grp_a, grp_b )
, grp_a
, grp_b
, count(*)
from
t2
group by
cube( grp_a, grp_b )
order by
1, 2, 3
;
```
```GROUPING_ID(GRP_A,GRP_B) GRP_A      GRP_B        COUNT(*)
------------------------ ---------- ---------- ----------
0 A1         X1                  1
0 A1         X2                  1
0 A1                             3
0 A2                             1
1 A1                             5
1 A2                             1
2            X1                  1
2            X2                  1
2                                4
3                                6
```

and then we wanted to exclude the empty set grouping (the one with a GROUPING_ID of "3"). We simply add a HAVING clause as follows.

```select
grouping_id( grp_a, grp_b )
, grp_a
, grp_b
, count(*)
from
t2
group by
cube( grp_a, grp_b )
HAVING
GROUPING_ID( GRP_A, GRP_B ) != 3
order by
1, 2, 3
;
```
```GROUPING_ID(GRP_A,GRP_B) GRP_A      GRP_B        COUNT(*)
------------------------ ---------- ---------- ----------
0 A1         X1                  1
0 A1         X2                  1
0 A1                             3
0 A2                             1
1 A1                             5
1 A2                             1
2            X1                  1
2            X2                  1
2                                4
```

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