SQL Snippets ™: Tutorials for Oracle Developers

When Oracle processes a GROUP BY query the WHERE clause is applied to the result set before the rows are grouped together. This allows us to use WHERE conditions involving columns like GRP_B in the query below, which is not listed in the GROUP BY clause.

select grp_a, count(*)
from   t
WHERE  GRP_B in ( 'b2', 'b3' )
group by grp_a
order by grp_a ;
 

GRP_A        COUNT(*)
---------- ----------
a1                  3
a2                  3
 

Thia does, however, prevent us from using conditions that involve aggregate values like COUNT(*) that are calculated after the GROUP BY clause is applied. For example, the following will not work.

select grp_a, count(*)
from   t
WHERE  COUNT(*) > 4
group by grp_a
order by grp_a ;
WHERE  COUNT(*) > 4
       *
ERROR at line 3:
ORA-00934: group function is not allowed here


 

For these types of conditions the HAVING clause can be used.

select grp_a, count(*)
from   t
group by grp_a
HAVING   COUNT(*) > 4
order by grp_a ;
 

GRP_A        COUNT(*)
---------- ----------
a1                  5
 

Note that the HAVING clause cannot reference table columns like VAL that are not listed in the GROUP BY clause.

select grp_a, count(*)
from   t
group by grp_a
HAVING   VAL > 5
order by grp_a ;
HAVING   VAL > 5
         *
ERROR at line 4:
ORA-00979: not a GROUP BY expression


 

It can, on the other hand, reference table columns like GRP_A that are in the GROUP BY clause.

select grp_a, count(*)
from   t
group by grp_a
HAVING   GRP_A = 'a2'
order by grp_a ;
 

GRP_A        COUNT(*)
---------- ----------
a2                  3
 

but doing so yields the same result as using a WHERE clause.

select grp_a, count(*)
from   t
WHERE  GRP_A = 'a2'
group by grp_a
order by grp_a ;
 

GRP_A        COUNT(*)
---------- ----------
a2                  3
 

Given a choice between the last two snippets I expect using a WHERE clause provides the best performance in most, if not all, cases.