hi,

my emp table data

select empno,ename,job,hiredate,grade,gradeon,deptno from emp;

     EMPNO ENAME      JOB       HIREDATE  GRADE GRADEON       DEPTNO
---------- ---------- --------- --------- ----- --------- ----------
      7369 SMITH      CLERK     17-DEC-80 1                       20
      7499 ALLEN      SALESMAN  20-FEB-81 1                       30
      7521 WARD       SALESMAN  22-FEB-81 1     22-FEB-81         30
      7566 JONES      MANAGER   02-APR-81 4                       20
      7654 MARTIN     SALESMAN  28-SEP-81 1                       30
      7698 BLAKE      MANAGER   01-MAY-81 4                       30
      7782 CLARK      MANAGER   09-JUN-81 4                       10
      7788 SCOTT      ANALYST   19-APR-87 2                       20
      7839 KING       PRESIDENT 17-NOV-81 5                       10
      7844 TURNER     SALESMAN  08-SEP-81 1                       30
      7876 ADAMS      CLERK     23-MAY-87 1                       20

     EMPNO ENAME      JOB       HIREDATE  GRADE GRADEON       DEPTNO
---------- ---------- --------- --------- ----- --------- ----------
      7900 JAMES      CLERK     03-DEC-81 1                       30
      7902 FORD       ANALYST   03-DEC-81 2     10-OCT-89         20
      7934 MILLER     CLERK     23-JAN-82                         10
      7988 STEVE      CLERK     23-JAN-07                         10
      8212 BRADY      ANALYST   23-JAN-06                         10
      9133 WILLIAM    CLERK     23-JAN-08                         20
      7902 FORD       MANAGER   03-DEC-81 3     23-NOV-95         20
      7902 FORD       MANAGER   03-DEC-81 3     23-NOV-95         20
      7902 FORD       CLERK     03-DEC-81 1     03-DEC-81         20
      7521 WARD       SALESMAN  22-FEB-81 1     22-FEB-81         30

21 rows selected.

now I executed the query below

SELECT   period_start_date,
         SUM (DECODE (deptno, 10, 1, 0)) AS v1,
         SUM (DECODE (deptno, 20, 1, 0)) AS val2,
         SUM (DECODE (deptno, 30, 1, 0)) AS val3
    FROM  (SELECT DISTINCT empno,
                          deptno,
                          hiredate,job,sal,comm,gradeon,grade
                     FROM emp) emp,
         (SELECT     ADD_MONTHS (TO_DATE ('1980-10-01', 'YYYY-MM-DD'),
                                 6 * (LEVEL - 1)
                                ) AS period_start_date
                FROM DUAL
          CONNECT BY LEVEL <= 100) dates
   WHERE emp.gradeon BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
     AND emp.hiredate <= SYSDATE 
GROUP BY period_start_date


output of the above query is

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          0          2
01-OCT-81          0          1          0
01-OCT-89          0          1          0
01-OCT-95          0          2          0

but my output should be as below

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          0          2
01-OCT-81          0          1          0
01-OCT-89          0          1          0
01-OCT-95          0          1          0

In the fourth row val2 column value should be 1 as the table contains 7902 repeated but it should be counted only once.

In the query above, I used inline query as below after from keyword

(SELECT DISTINCT empno,
                          deptno,
                          hiredate,job,sal,comm,gradeon,grade
                     FROM emp) emp
I need all the fields which I specified above.
If I remove 1 or 2 fields then I am getting the output expected.

thanks

sorry,

It is emp.hiredate between