## Grouping Rows with GROUP BY

### Gotcha: GROUPING SETS with Constants

When I first started using GROUPING SETS I used constants to produce grand total rows in my result sets, like this.

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

The last row in the result set is generated by the "0" grouping. I later learnt that an empty set term, "()", was actually a more appropriate syntactic choice than a constant but I continued to use constants out of habit. After all, both approaches seemed to produce the same results.

```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                             5
a2                             3
b1                  2
b2                  3
b3                  3
8
```

However, I later ran into a case where the two actually produced different results.

Query 1 Query 2
```set null '(null)'

select
grp_a
, grp_b
, nvl2( grp_b, 1, 0 ) nvl2_grp_b
, count(*)
from
t
GROUP BY
GROUPING SETS ( GRP_A, GRP_B, () )
order by
grp_a, grp_b
;

GRP_A  GRP_B  NVL2_GRP_B   COUNT(*)
------ ------ ---------- ----------
a1     (null)          0          5
a2     (null)          0          3
(null) b1              1          2
(null) b2              1          3
(null) b3              1          3
(null) (null)          0          8

```
```set null '(null)'

select
grp_a
, grp_b
, nvl2( grp_b, 1, 0 ) nvl2_grp_b
, count(*)
from
t
GROUP BY
GROUPING SETS ( GRP_A, GRP_B, 0 )
order by
grp_a, grp_b
;

GRP_A  GRP_B  NVL2_GRP_B   COUNT(*)
------ ------ ---------- ----------
a1     (null) (null)              5
a2     (null) (null)              3
(null) b1              1          2
(null) b2              1          3
(null) b3              1          3
(null) (null)          0          8
```

Note how Query 2 returns "(null)" in the NVL2_GRP_B column and Query 1 does not. This is because "0" appears in both the SELECT list and the GROUP BY clause. Readers who want to understand more about why these two queries differ can reverse engineer the two into their UNION ALL equivalents using the instructions at Reverse Engineering GROUPING BY Queries. Readers who don't simply need to remember this rule of thumb - always use an empty set term to generate a grand total row, do not use a constant.

#### 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-13126.html]SQL Snippets: Grouping Rows with GROUP BY - Gotcha: GROUPING SETS with Constants[/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-13126.html">SQL Snippets: Grouping Rows with GROUP BY - Gotcha: GROUPING SETS with Constants</a>

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

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