Re: group function not allowed in where clause?

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sat, 19 Jun 2004 07:59:29 +1000
Message-ID: <40d365a7$0$18190$afc38c87@news.optusnet.com.au>

"Marc Eggenberger" <marc.eggenberger@_REMOVE_itc.alstom.com> wrote in message news:MPG.1b3d1934526ef0e698968d_at_10.128.18.49...
> In article <MPG.1b3d1719d1792ee198968c_at_10.128.18.49>,
> marc.eggenberger@_REMOVE_itc.alstom.com says...
>
> [...]
>
> Stupid me ... must use having clause ... Must be friday ;)
>
> --
> mfg
> Marc

I didn't look closely at your example, but you might also want to investigate the WITH clause, which will often perform better than a having clause. The WITH clause stores your aggregation results in a temporary table, and you can then reference it in your code as you would any normal table. For example:

Instead of this:

SQL> select deptno, sum(sal) as dept_total   2 from emp
  3 group by deptno
  4 having sum(sal) >

  5     (select sum(sal) *0.35
  6     from emp)

  7 order by sum(sal);

...you could write this:

SQL> with deptsum as (

  2          select deptno, sum(sal) as dept_total
  3          from emp
  4          group by deptno),
  5       grandtotals as (
  6          select sum(dept_total) as grand_total from deptsum)

  7 select deptno, dept_total from deptsum   8 where dept_total>(select (grand_total*.35) from grandtotals)   9 order by dept_total;

Regards
HJR Received on Fri Jun 18 2004 - 16:59:29 CDT