Home » SQL & PL/SQL » SQL & PL/SQL » counting number of records (merged)
counting number of records (merged) [message #250393] Mon, 09 July 2007 22:08 Go to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
Hi,

I have emp table with the data below.(my empno is not a primarykey, I have some other field as primary key)

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

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

     EMPNO ENAME      JOB       HIREDATE      DEPTNO
---------- ---------- --------- --------- ----------
      7900 JAMES      CLERK     03-DEC-81         30
      7902 FORD       ANALYST   03-DEC-81         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       ANALYST   03-DEC-81         20
      7521 WARD       SALESMAN  22-FEB-81         30

19 rows selected.

empno 7902 and 7521 are repeated in the above output.so when
I execute the query below then those empnos should be counted once only.
The query below is counting twice.

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     scott.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.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6) and
hiredate <= sysdate
GROUP BY period_start_date


output of above query is

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          3
01-APR-81          1          1          3
01-OCT-81          2          2          1
01-APR-87          0          2          0

In the above output 1st row val3 count is 3,but it should be 2 because 7521 is repeated so it should be counted only once.
3rd row also value of val2 is 2 as 7902 empno is twice,but it should be counted only once.
my output should be as below

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          2
01-APR-81          1          1          3
01-OCT-81          2          1          1
01-APR-87          0          2          0

thanks


Re: problem regarding count of records [message #250449 is a reply to message #250393] Tue, 10 July 2007 01:30 Go to previous messageGo to next message
sehgal.best
Messages: 7
Registered: February 2007
Junior Member
You are using the query in a right manner.
If you want to use the distinct records fetch or use the logice
that fetches distinct records.

Use something like
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
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.hiredate BETWEEN dates.period_start_date
AND ADD_MONTHS (dates.period_start_date, 6)
AND hiredate <= SYSDATE
GROUP BY period_start_date
ORDER BY period_start_date;


----
review the above query for performance as per your requirement
Re: problem regarding count of records [message #250454 is a reply to message #250449] Tue, 10 July 2007 01:37 Go to previous messageGo to next message
sehgal.best
Messages: 7
Registered: February 2007
Junior Member
Sorry senior members
I think i should have formatted it. Following is the query.

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
                     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.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
     AND hiredate <= SYSDATE
GROUP BY period_start_date
ORDER BY period_start_date;

Re: problem regarding count of records [message #250460 is a reply to message #250393] Tue, 10 July 2007 02:20 Go to previous messageGo to next message
stevefaulk
Messages: 36
Registered: June 2007
Member
thanks
Re: problem regarding count of records [message #250712 is a reply to message #250454] Wed, 11 July 2007 01:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
sehgal.best wrote on Tue, 10 July 2007 08:37
Sorry senior members
I think i should have formatted it.

Formatting is not done as a favour to senior members...

[Updated on: Wed, 11 July 2007 01:00]

Report message to a moderator

counting number of records [message #251576 is a reply to message #250393] Sat, 14 July 2007 23:50 Go to previous message
stevefaulk
Messages: 36
Registered: June 2007
Member
Hi,

my previous requirement was as 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
                     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.hiredate BETWEEN dates.period_start_date
                          AND ADD_MONTHS (dates.period_start_date, 6)
     AND hiredate <= SYSDATE
GROUP BY period_start_date;

the output of above query is

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          1          2
01-APR-81          1          1          3
01-OCT-81          2          1          1
01-APR-87          0          2          0
01-OCT-05          1          0          0
01-OCT-06          1          0          0

6 rows selected.

Now my emp table contains the data as below
EMPNO is not primary key, I have some other field as primary key.

     EMPNO ENAME      JOB       HIREDATE      DEPTNO GRADE GRADEON
---------- ---------- --------- --------- ---------- ----- ---------
      7521 WARD       SALESMAN  22-FEB-81         30 1     22-FEB-81
      7902 FORD       ANALYST   03-DEC-81         20 2     10-OCT-89
      7902 FORD       MANAGER   03-DEC-81         20 3     23-NOV-95
      7902 FORD       MANAGER   03-DEC-81         20 3     23-NOV-95
      7902 FORD       CLERK     03-DEC-81         20 1     03-DEC-81
      7521 WARD       SALESMAN  22-FEB-81         30 1     22-FEB-81

6 rows selected.

my expected output is

PERIOD_ST         V1       VAL2       VAL3
--------- ---------- ---------- ----------
01-OCT-80          0          0          0
01-APR-81          0          0          0
01-OCT-81          0          1          0
01-APR-87          0          0          0
01-OCT-05          0          0          0
01-OCT-06          0          0          0

6 rows selected.

my table data is showing grade and gradeon date fields along with other fields,if the grade changes only then it should be counted as 1 else 0.
employee WARD grade is not changed though WARD record is twice.
so it should be counted as 0.
WARD's hiredate falls under range 01-OCT-80 and 01-APR-81 which is 1st row and deptno is 30.
so 1st row third coulmn value is 0.

FORD grade has been changed twice,so count is one only 
though changed twice and it falls under period_start_date range
01-OCT-81 to 03-MAR-82, which is row 3 in above data.
Gradeon is the date on which grade is awarded to that employee.

thanks
Previous Topic: Help required in returning a cursor of varray
Next Topic: NULL values & UNIQUE Key
Goto Forum:
  


Current Time: Tue Dec 06 08:37:01 CST 2016

Total time taken to generate the page: 0.20846 seconds