Re: Group by: Not getting expected output

From: <mjbox01_at_gmail.com>
Date: 12 May 2006 06:04:17 -0700
Message-ID: <1147439057.439584.179870@i39g2000cwa.googlegroups.com>

Carlos wrote:
> >>I doubt very much it does.
>
> You, faithless man! ;-)
>
> Of course it does:
>
> <snip>
>
> A long time ago in a galaxy far, far away (Oracle 7)... there were no
> CASE sentences...

And if you wanted to do it efficiently in Oracle 7 without reading the table twice, you could use decode and sign.

SQL> select deptno,

  2      count(decode(sign(sal - 1201), -1, null,
  3              decode(sign(sal - 2000), -1, 1))) count

  4 from emp
  5 group by deptno
  6 order by deptno;

    DEPTNO COUNT
---------- ----------

        10          1
        20          0
        30          4

Received on Fri May 12 2006 - 08:04:17 CDT