Re: Group by: Not getting expected output

Well they are the expected results so there is something wrong with your expectations which could probably be resolved by reading the SQL Reference manual.

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm

To get your required results you can use case

SQL> select * from emp;

 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

------ ---------- --------- ------ ---------- ------ ------ ------
  7369 SMITH      CLERK       7902 12-17-1980    800            20
  7499 ALLEN      SALESMAN    7698 02-20-1981   1600    300     30
  7521 WARD       SALESMAN    7698 02-22-1981   1250    500     30
  7566 JONES      MANAGER     7839 04-02-1981   2975            20
  7654 MARTIN     SALESMAN    7698 09-28-1981   1250   1400     30
  7698 BLAKE      MANAGER     7839 05-01-1981   2850            30
  7782 CLARK      MANAGER     7839 06-09-1981   2450            10
  7788 SCOTT      ANALYST     7566 12-09-1982   3000            20
  7839 KING       PRESIDENT        11-17-1981   5000            10
  7844 TURNER     SALESMAN    7698 09-08-1981   1500      0     30
  7876 ADAMS      CLERK       7788 01-12-1983   1100            20
  7900 JAMES      CLERK       7698 12-03-1981    950            30
  7902 FORD       ANALYST     7566 12-03-1981   3000            20
  7934 MILLER     CLERK       7782 01-23-1982   1300            10

14 rows selected.

SQL> select deptno,
  2 count(case when sal >= 1200 and sal < 2000 then sal end)   3 from emp
  4 group by deptno
  5 order by deptno;

DEPTNO COUNT(CASEWHENSAL>=1200ANDSAL<2000THENSALEND)

------ ---------------------------------------------
    10                                             1
    20                                             0
    30                                             4

SQL> Received on Wed May 10 2006 - 06:59:44 CDT