Home » SQL & PL/SQL » SQL & PL/SQL » Restriction on Analytical functions (Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production)
Restriction on Analytical functions [message #572098] Thu, 06 December 2012 01:35 Go to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member

Hi All,

Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .
AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .

For Ex: Departments wise count including all employees record :
SQL> select count(*) over(partition by deptno) dept_Count, ce.*
  2    from scott.emp ce
  3   order by deptno, job;
   
DEPT_COUNT EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         3  7934 MILLER     CLERK      7782 1/23/1982     1300.00               10
         3  7782 CLARK      MANAGER    7839 6/9/1981      2450.00               10
         3  7839 KING       PRESIDENT       11/17/1981    5000.00               10
         5  7788 SCOTT      ANALYST    7566 4/19/1987     3000.00               20
         5  7902 FORD       ANALYST    7566 12/3/1981     3000.00               20
         5  7876 ADAMS      CLERK      7788 5/23/1987     1100.00               20
         5  7369 SMITH      CLERK      7902 12/17/1980     800.00               20
         5  7566 JONES      MANAGER    7839 4/2/1981      2975.00               20
         6  7900 JAMES      CLERK      7698 12/3/1981      950.00               30
         6  7698 BLAKE      MANAGER    7839 5/1/1981      2850.00               30
         6  7654 MARTIN     SALESMAN   7698 9/28/1981     1250.00   1400.00     30
         6  7521 WARD       SALESMAN   7698 2/22/1981     1250.00    500.00     30
         6  7499 ALLEN      SALESMAN   7698 2/20/1981     1600.00    300.00     30
         6  7844 TURNER     SALESMAN   7698 9/8/1981      1500.00      0.00     30 



But I need to get the only CLERK count in each department including the all employees record .
Required out put :

SQL> select count(*) over(partition by deptno /* HAVING JOB LIKE  'CLERK' */) dept_CLERK_Count, ce.*
  2    from scott.emp ce
  3   order by deptno, job;
   
DEPT_COUNT EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         1  7934 MILLER     CLERK      7782 1/23/1982     1300.00               10
         1  7782 CLARK      MANAGER    7839 6/9/1981      2450.00               10
         1  7839 KING       PRESIDENT       11/17/1981    5000.00               10
         2  7788 SCOTT      ANALYST    7566 4/19/1987     3000.00               20
         2  7902 FORD       ANALYST    7566 12/3/1981     3000.00               20
         2  7876 ADAMS      CLERK      7788 5/23/1987     1100.00               20
         2  7369 SMITH      CLERK      7902 12/17/1980     800.00               20
         2  7566 JONES      MANAGER    7839 4/2/1981      2975.00               20
         1  7900 JAMES      CLERK      7698 12/3/1981      950.00               30
         1  7698 BLAKE      MANAGER    7839 5/1/1981      2850.00               30
         1  7654 MARTIN     SALESMAN   7698 9/28/1981     1250.00   1400.00     30
         1  7521 WARD       SALESMAN   7698 2/22/1981     1250.00    500.00     30
         1  7499 ALLEN      SALESMAN   7698 2/20/1981     1600.00    300.00     30
         1  7844 TURNER     SALESMAN   7698 9/8/1981      1500.00      0.00     30 



Thanks
SaiPradyumn

Re: Restriction on Analytical functions [message #572100 is a reply to message #572098] Thu, 06 December 2012 01:45 Go to previous messageGo to next message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Michel ,

I have an alternate solution by using following query. But i can't apply this logic for my actual requirement as I have more number of tables which I have to join :
select (select count(*)
          from scott.emp se2
         where se.deptno = se2.deptno
           and se2.job = 'CLERK') dept_clerk_Count,
       se.*
  from scott.emp se
   order by deptno, job


Please provide the best solution

Thanks
SaiPradyumn
Re: Restriction on Analytical functions [message #572101 is a reply to message #572098] Thu, 06 December 2012 01:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put the query in an inline view and apply the restriction on it.
select ... from (<your query>) where <your restriction on analytic result>;

Regards
Michel
Re: Restriction on Analytical functions [message #572103 is a reply to message #572098] Thu, 06 December 2012 01:48 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry misread this:

Quote:
But I need to get the only CLERK count in each department including the all employees record .


So:
count(decode(job,'CLERK',1)) over(partition by deptno)

Regards
Michel
Re: Restriction on Analytical functions [message #572107 is a reply to message #572103] Thu, 06 December 2012 02:08 Go to previous message
saipradyumn
Messages: 187
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Michel ,

Thank U very much for your efficient solution .

Thanks
SaiPradyumn
Previous Topic: DEADLOCK FOUND
Next Topic: Can we use the temp table in the function which uses ref cursor
Goto Forum:
  


Current Time: Sat Oct 25 00:51:03 CDT 2014

Total time taken to generate the page: 0.04678 seconds