could anybody explain me with an example an anyalatical function in 8i.an exaample would be nice
thanx in advance
Printable View
could anybody explain me with an example an anyalatical function in 8i.an exaample would be nice
thanx in advance
the docs unfortunatelt aint contain any examples which i badly need
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
Sam:
Thanks! I'd appreciate more info on these functions also. Please email to oracle_dev2000@hotmail.com
Thanks!
Since I have already poste the set 1, I did not send that to you.
Haev fun :)
Sam
Sam:
Thanks. It looks like these functions can really be time/complexity savers.
I'll post how I use them later.
Good luck :D
Sam
i have never been disappointed whenever i post my queries here gr8 folks like sam make this forum really interesting thanx folks
sambavan could you please send me more examples in case you ahve them to the following adress at hrishys@yahoo.co.uk