analyatical functions in 8i
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: analyatical functions in 8i

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828
    could anybody explain me with an example an anyalatical function in 8i.an exaample would be nice

    thanx in advance

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    [url]http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a85397/function.htm#81409[/url]

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    the docs unfortunatelt aint contain any examples which i badly need

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Here you go on some of the functions:Oracle8i has included two new features, ROLLUP and CUBE,
    for data summarization. They're designed to provide
    additional levels of grouping in a query.

    Let's see how this works.

    Here's a listing of the rows in the EMP table of Oracle's
    demo account, SCOTT:

    SQL> select deptno, empno, ename, job, sal
    from emp order by deptno, empno;

    DEPTNO EMPNO ENAME JOB SAL
    --------- --------- ---------- --------- ---------
    10 7782 CLARK MANAGER 2450
    10 7839 KING PRESIDENT 5000

    10 7934 MILLER CLERK 1300
    20 7369 SMITH CLERK 800
    20 7566 JONES MANAGER 2975
    20 7788 SCOTT ANALYST 3000
    20 7876 ADAMS CLERK 1100
    20 7902 FORD ANALYST 3000
    30 7499 ALLEN SALESMAN 1600
    30 7521 WARD SALESMAN 1250
    30 7654 MARTIN SALESMAN 1250
    30 7698 BLAKE MANAGER 2850
    30 7844 TURNER SALESMAN 1500
    30 7900 JAMES CLERK 950

    14 rows selected.

    If we wish to find the number of employees by department,
    we issue the query:

    SQL> select deptno, count(*)
    from emp
    group by deptno;

    DEPTNO COUNT(*)
    --------- ---------
    10 3
    20 5
    30 6

    If we also wish to include the total number of employees
    in the output, we need to include the UNION clause, as
    shown here:

    SQL> select deptno, count(*)
    from emp
    group by deptno
    union
    select to_number(null), count(*)
    from emp;

    DEPTNO COUNT(*)
    --------- ---------
    10 3
    20 5
    30 6
    14

    Starting from Oracle8i, we can use the ROLLUP option to
    achieve the same result:

    SQL> select deptno, count(*)
    from emp
    group by rollup(deptno);


    DEPTNO COUNT(*)
    --------- ---------
    10 3
    20 5
    30 6
    14


    By simply adding the invoking ROLLUP facility in the
    GROUP BY clause, we direct Oracle to summarize the data
    at levels above the columns specified, all the way to the
    grand total.

    Note that Oracle reports the grand total by leaving the
    GROUP BY column blank. If the GROUP BY column also
    contains null values, it might be difficult to
    distinguish those values from the grand total row.
    Fortunately, we can use a special function called
    GROUPING to report the current summarization level. The
    function returns two values: 0 indicates the current row
    is grouped at the specified GROUP BY level, and 1
    indicates the row is grouped at a higher level.

    SQL> select deptno, count(*), grouping(deptno)
    from emp
    group by rollup(deptno);

    DEPTNO COUNT(*) GROUPING(DEPTNO)
    --------- --------- ----------------
    10 3 0
    20 5 0
    30 6 0
    14 1

    Now we can take advantage of the GROUPING function to
    format the output:

    SQL> select decode(grouping(deptno),0,to_char(deptno),'Total') deptno, count(*)
    from emp
    group by rollup(deptno);



    DEPTNO COUNT(*)
    ---------------------------------------- ---------
    10 3
    20 5
    30 6
    Total 14

    The ROLLUP feature can in fact be applied to multiple
    columns. The result is multiple levels of rollup, as
    illustrated here:

    SQL> select deptno, job, count(*), grouping(deptno), grouping(job)
    from emp
    group by rollup(deptno, job);

    DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB)
    --------- --------- --------- ---------------- -------------
    10 CLERK 1 0 0
    10 MANAGER 1 0 0
    10 PRESIDENT 1 0 0
    10 3 0 1
    20 ANALYST 2 0 0
    20 CLERK 2 0 0
    20 MANAGER 1 0 0
    20 5 0 1
    30 CLERK 1 0 0
    30 MANAGER 1 0 0
    30 SALESMAN 4 0 0
    30 6 0 1
    14 1 1

    As shown in this example, we're able to count the
    employees by 1) department and job; 2) department; and 3)
    grand total.Similar to ROLLUP is the CUBE function. CUBE groups data
    at multiple dimensions. In particular, it summarizes data
    based on all possible combinations of the columns
    specified in the GROUP BY clause. Let's see how this
    works:

    SQL> select deptno, job, count(*), grouping(deptno), grouping(job)
    from emp
    group by cube(deptno, job);

    DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB)
    --------- --------- --------- ---------------- -------------
    10 CLERK 1 0 0
    10 MANAGER 1 0 0
    10 PRESIDENT 1 0 0
    10 3 0 1
    20 ANALYST 2 0 0
    20 CLERK 2 0 0
    20 MANAGER 1 0 0
    20 5 0 1
    30 CLERK 1 0 0
    30 MANAGER 1 0 0
    30 SALESMAN 4 0 0
    30 6 0 1
    ANALYST 2 1 0
    CLERK 4 1 0
    MANAGER 3 1 0
    PRESIDENT 1 1 0
    SALESMAN 4 1 0
    14 1 1

    The CUBE function includes the output generated by
    ROLLUP. In addition, it includes the number of employees
    by job---independent of the department number.

    In summary, the ROLLUP and CUBE features greatly simplify
    SQL coding. We no longer have to spend hours trying to
    come up with those UNION statements ourselves!

    IF you need more on this kind of examples, let me know of your email address and I'll send them to you.

    Hope this one would help you.
    Sam

    Thanx
    Sam



    Life is a journey, not a destination!


  5. #5
    Join Date
    Aug 2000
    Posts
    462
    Sam:

    Thanks! I'd appreciate more info on these functions also. Please email to oracle_dev2000@hotmail.com

    Thanks!


  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Since I have already poste the set 1, I did not send that to you.

    Haev fun :)
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  7. #7
    Join Date
    Aug 2000
    Posts
    462
    Sam:

    Thanks. It looks like these functions can really be time/complexity savers.

    I'll post how I use them later.

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Good luck :D

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  9. #9
    Join Date
    Jan 2001
    Posts
    2,828

    Wink

    i have never been disappointed whenever i post my queries here gr8 folks like sam make this forum really interesting thanx folks

  10. #10
    Join Date
    Jan 2001
    Posts
    2,828
    sambavan could you please send me more examples in case you ahve them to the following adress at hrishys@yahoo.co.uk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width