OracleTuts Oracle Training, Tutorials, and Videos from Beginner to Advanced
A while ago I wrote several similar articles on how to use the different Analytic functions in Oracle. I have now chosen to merge these articles into one useful article/document that you can print out or save to your computer for your own reference.
In this tutorial I will try to explain to you how to use them, through practical examples. You may have some problems following this tutorial if you do not have knowledge about Oracle’s aggregated functions.
An analytic function differs from a regular aggregated function (like i.e. SUM, AVG, MAX, MIN, etc) in the way that they return multiple rows for each group. The group of rows is called a window. In a way you can think about it as if you have a resultset from your query, and for each record there is a little window to set of other data.
All the samples in this tutorial are using the tables in the default SCOTT schema that follows a standard Oracle database installation. This schema is not always available in your database, depending on the administrative database setup. And, it might not be available in newer versions of Oracle. You can i.e. find the scripts here: Oracle SCOTT demo tables.
The common format of the use of an analytic funtion is as follows:
analytic_function(arguments) OVER (PARTITION BY <partition_by_fields> ORDER BY <order_by_fields>) [<window_clause>]
The syntax can vary a little bit based on which of the analytic functions it is.
If you have a big interest for details you can read about Oracle’s Analytic Functions (and everything else about Oracle SQL) in the SQL Reference, found in the Oracle Database Documentation Library .
UPDATE 6/27/2012: I added a post on how to use the RANK() and DENSE_RANK() analytic functions: How To Rank Records in Oracle SQL
COUNT aggregate function sample
Consider this use of the COUNT aggregate function
SQL> select e.deptno 2 ,count(e.empno) emp_count 3 from emp e 4 group by e.deptno 5 / DEPTNO EMP_COUNT ------ ---------- 30 6 20 5 10 3
As you can see, for each of the departments in the resultset you will find one number.
COUNT analytic function sample
Now, let us use the COUNT analytic function and se what happens:
SQL> select e.deptno 2 ,e.empno 3 ,count(e.empno) over (partition by e.deptno) emp_count 4 from emp e 5 order by e.deptno, e.empno 6 / DEPTNO EMPNO EMP_COUNT ------ ----- ---------- 10 7782 3 10 7839 3 10 7934 3 20 7369 5 20 7566 5 20 7788 5 20 7876 5 20 7902 5 30 7499 6 30 7521 6 30 7654 6 30 7698 6 30 7844 6 30 7900 6
I added the empno field to the query so that you easier can see what happens in the resultset. And, as you can see the result set shows the count of the number of employees in each department…for EACH of the employees in the department.
The equivalent query using the COUNT aggregate function would be:
SQL> select e.deptno 2 ,e.empno 3 ,( 4 select count(e.empno) 5 from emp e2 6 where e2.deptno = e.deptno 7 ) emp_count 8 from emp e 9 order by e.deptno, e.empno 10 / DEPTNO EMPNO EMP_COUNT ------ ----- ---------- 10 7782 3 10 7839 3 10 7934 3 20 7369 5 20 7566 5 20 7788 5 20 7876 5 20 7902 5 30 7499 6 30 7521 6 30 7654 6 30 7698 6 30 7844 6 30 7900 6
Ok, so how can we practically use the COUNT analytic funtion?
COUNT analytic function, practical example
Let us pretend that the HR department wants to know how many employees there are in each department in our company, and also how many percent the count of department employees is out of the total employees. This is one way you can do this using the COUNT analytic function:
SQL> select distinct e.deptno 2 ,count(e.empno) over (partition by e.deptno) dept_emp_count 3 ,count(e.empno) over (partition by 1) overall_emp_count 4 ,round(( 5 (count(e.empno) over (partition by e.deptno) * 100) / count(e.empno) over (partition by 1) 6 ), 2) deptno_emp_perc 7 from emp e 8 / DEPTNO DEPT_EMP_COUNT OVERALL_EMP_COUNT DEPTNO_EMP_PERC ------ -------------- ----------------- --------------- 20 5 14 35.71 30 6 14 42.86 10 3 14 21.43
In the query above you will see a “trick” that I use many times in my queries containing analytic function: partition by 1. This simply means every record in the resultset..
SUM aggregate function sample
Consider this use of the SUM aggregate function:
SQL> select e.deptno 2 ,sum(e.sal) emp_salary_sum 3 from emp e 4 group by e.deptno 5 order by e.deptno 6 / DEPTNO EMP_SALARY_SUM ------ -------------- 10 8750 20 10875 30 9400
As you can see, for each of the departments in the resultset you will find one number.
SUM analytic function sample
Now, let us use the SUM analytic function and se what happens:
SQL> select e.deptno 2 ,e.empno 3 ,e.sal 4 ,sum(e.sal) over (partition by e.deptno) emp_salary_sum 5 from emp e 6 order by e.deptno, e.empno 7 / DEPTNO EMPNO SAL EMP_SALARY_SUM ------ ----- --------- -------------- 10 7782 2450.00 8750 10 7839 5000.00 8750 10 7934 1300.00 8750 20 7369 800.00 10875 20 7566 2975.00 10875 20 7788 3000.00 10875 20 7876 1100.00 10875 20 7902 3000.00 10875 30 7499 1600.00 9400 30 7521 1250.00 9400 30 7654 1250.00 9400 30 7698 2850.00 9400 30 7844 1500.00 9400 30 7900 950.00 9400
I added the empno field to the query so that you easier can see what happens in the resultset. And, as you can see the result set shows the sum of the amount of salary for the employees in each department…for EACH of the employees in the department.
The equivalent query using the SUM aggregate function would be:
SQL> select e.deptno 2 ,e.empno 3 ,e.sal 4 ,( 5 select sum(e2.sal) 6 from emp e2 7 where e2.deptno = e.deptno 8 ) sum_dept_salary 9 from emp e 10 order by e.deptno, e.empno 11 / DEPTNO EMPNO SAL SUM_DEPT_SALARY ------ ----- --------- --------------- 10 7782 2450.00 8750 10 7839 5000.00 8750 10 7934 1300.00 8750 20 7369 800.00 10875 20 7566 2975.00 10875 20 7788 3000.00 10875 20 7876 1100.00 10875 20 7902 3000.00 10875 30 7499 1600.00 9400 30 7521 1250.00 9400 30 7654 1250.00 9400 30 7698 2850.00 9400 30 7844 1500.00 9400 30 7900 950.00 9400
Ok, so how can we practically use the SUM analytic funtion?
SUM analytic function, practical example
Let us pretend that the HR department wants to know how much money each of departments in our company make, and also how many percent this is out of the total infome for the whole comapny. This is one way you can do this using the SUM analytic function:
SQL> select distinct e.deptno 2 ,sum(e.sal) over (partition by e.deptno) dept_emp_salary 3 ,sum(e.sal) over (partition by 1) overall_emp_salary 4 ,round(( 5 (sum(e.sal) over (partition by e.deptno) * 100) / sum(e.sal) over (partition by 1) 6 ), 2) deptno_emp_perc 7 from emp e 8 / DEPTNO DEPT_EMP_SALARY OVERALL_EMP_SALARY DEPTNO_EMP_PERC ------ --------------- ------------------ --------------- 10 8750 29025 30.15 20 10875 29025 37.47 30 9400 29025 32.39
In the query above you will see a “trick” that I use many times in my queries containing analytic function: partition by 1. This simply means every record in the resultset..
AVG aggregate function sample
Consider this use of the AVG aggregate function:
SQL> select e.deptno 2 ,round(avg(e.sal), 2) avg_salary 3 from emp e 4 group by e.deptno 5 order by e.deptno 6 / DEPTNO AVG_SALARY ------ ---------- 10 2916.67 20 2175 30 1566.67
As you can see, for each of the departments in the resultset you will find one number.
AVG analytic function sample
Now, let us use the AVG analytic function and se what happens:
SQL> select e.deptno 2 ,e.empno 3 ,e.sal 4 ,round(avg(e.sal) over (partition by e.deptno), 2) emp_salary_avg 5 from emp e 6 order by e.deptno, e.empno 7 / DEPTNO EMPNO SAL EMP_SALARY_AVG ------ ----- --------- -------------- 10 7782 2450.00 2916.67 10 7839 5000.00 2916.67 10 7934 1300.00 2916.67 20 7369 800.00 2175 20 7566 2975.00 2175 20 7788 3000.00 2175 20 7876 1100.00 2175 20 7902 3000.00 2175 30 7499 1600.00 1566.67 30 7521 1250.00 1566.67 30 7654 1250.00 1566.67 30 7698 2850.00 1566.67 30 7844 1500.00 1566.67 30 7900 950.00 1566.67
I added the empno field to the query so that you easier can see what happens in the resultset. And, as you can see the result set shows the avg of the amount of salary for the employees in each department…for EACH of the employees in the department.
The equivalent query using the AVG aggregate function would be:
SQL> SELECT e.deptno 2 ,e.empno 3 ,e.sal 4 ,( 5 SELECT ROUND(AVG(e2.sal), 2) 6 FROM scott.emp e2 7 WHERE e2.deptno = e.deptno 8 ) sum_dept_salary 9 FROM scott.emp e 10 ORDER BY e.deptno, e.empno 11 / DEPTNO EMPNO SAL SUM_DEPT_SALARY ------ ----- --------- --------------- 10 7782 2450.00 2916.67 10 7839 5000.00 2916.67 10 7934 1300.00 2916.67 20 7369 800.00 2175 20 7566 2975.00 2175 20 7788 3000.00 2175 20 7876 1100.00 2175 20 7902 3000.00 2175 30 7499 1600.00 1566.67 30 7521 1250.00 1566.67 30 7654 1250.00 1566.67 30 7698 2850.00 1566.67 30 7844 1500.00 1566.67 30 7900 950.00 1566.67
Ok, so how can we practically use the AVG analytic funtion?
AVG analytic function, practical example
Let us pretend that the HR department wants to know how much money, in average, each of departments in our company make, and also how many percent this is out of the total income for the whole company. This is one way you can do this using the AVG analytic function:
SQL> select distinct e.deptno 2 ,round(avg(e.sal) over (partition by e.deptno), 2) avg_dept_salary 3 ,round(avg(e.sal) over (partition by 1), 2) avg_overall_salary 4 ,round(((avg(e.sal) over (partition by e.deptno) * 100) / avg(e.sal) over (partition by 1)), 2) avg_perc_of_avg_tot 5 from emp e 6 / DEPTNO AVG_DEPT_SALARY AVG_OVERALL_SALARY AVG_PERC_OF_AVG_TOT ------ --------------- ------------------ ------------------- 10 2916.67 2073.21 140.68 30 1566.67 2073.21 75.57 20 2175 2073.21 104.91
In the query above you will see a “trick” that I use many times in my queries containing analytic function: partition by 1. This simply means every record in the resultset..
MIN aggregate function sample
Consider this use of the MIN aggregate function:
SQL> SELECT e.deptno 2 ,ROUND(MIN(e.sal), 2) min_salary 3 FROM scott.emp e 4 GROUP BY e.deptno 5 ORDER BY e.deptno 6 / DEPTNO MIN_SALARY ------ ---------- 10 1300 20 800 30 950
As you can see, for each of the departments in the resultset you will find one number.
MIN analytic function sample
Now, let us use the MIN analytic function and se what happens:
SQL> SELECT e.deptno 2 ,e.empno 3 ,e.sal 4 ,MIN(e.sal) OVER (PARTITION BY e.deptno) emp_salary_min 5 FROM scott.emp e 6 ORDER BY e.deptno, e.empno 7 / DEPTNO EMPNO SAL EMP_SALARY_MIN ------ ----- --------- -------------- 10 7782 2450.00 1300 10 7839 5000.00 1300 10 7934 1300.00 1300 20 7369 800.00 800 20 7566 2975.00 800 20 7788 3000.00 800 20 7876 1100.00 800 20 7902 3000.00 800 30 7499 1600.00 950 30 7521 1250.00 950 30 7654 1250.00 950 30 7698 2850.00 950 30 7844 1500.00 950 30 7900 950.00 950
I added the empno field to the query so that you easier can see what happens in the resultset. And, as you can see the result set shows the min salary for the employees in each department…for EACH of the employees in the department.
The equivalent query using the MIN aggregate function would be:
SQL> SELECT e.deptno 2 ,e.empno 3 ,e.sal 4 ,( 5 SELECT ROUND(MIN(e2.sal), 2) 6 FROM scott.emp e2 7 WHERE e2.deptno = e.deptno 8 ) sum_dept_salary 9 FROM scott.emp e 10 ORDER BY e.deptno, e.empno 11 / DEPTNO EMPNO SAL SUM_DEPT_SALARY ------ ----- --------- --------------- 10 7782 2450.00 1300 10 7839 5000.00 1300 10 7934 1300.00 1300 20 7369 800.00 800 20 7566 2975.00 800 20 7788 3000.00 800 20 7876 1100.00 800 20 7902 3000.00 800 30 7499 1600.00 950 30 7521 1250.00 950 30 7654 1250.00 950 30 7698 2850.00 950 30 7844 1500.00 950 30 7900 950.00 950
Ok, so how can we practically use the MIN analytic funtion?
MIN analytic function, practical example
Let us pretend that the HR department wants to know how many employees in each department that make more than the minimum salary for the department. This is one way you can do this using the MIN analytic function (together with an outer SUM aggregate function):
SQL> SELECT x.deptno 2 ,x.dept_emp_salary_min 3 ,SUM(CASE 4 WHEN x.sal > dept_emp_salary_min THEN 1 5 ELSE 0 6 END) dept_emp_sal_above_min 7 FROM ( 8 SELECT e.deptno 9 ,e.sal 10 ,MIN(e.sal) OVER (PARTITION BY e.deptno) dept_emp_salary_min 11 FROM scott.emp e 12 ) x 13 GROUP BY x.deptno 14 ,x.dept_emp_salary_min 15 / DEPTNO DEPT_EMP_SALARY_MIN DEPT_EMP_SAL_ABOVE_MIN ------ ------------------- ---------------------- 20 800 4 30 950 5 10 1300 2
MAX aggregate function sample
Consider this use of the MAX aggregate function:
SQL> SELECT e.deptno 2 ,ROUND(MAX(e.sal), 2) max_salary 3 FROM scott.emp e 4 GROUP BY e.deptno 5 ORDER BY e.deptno 6 / DEPTNO MAX_SALARY ------ ---------- 10 5000 20 3000 30 2850
As you can see, for each of the departments in the resultset you will find one number.
MAX analytic function sample
Now, let us use the MAX analytic function and se what happens:
SQL> SELECT e.deptno 2 ,e.empno 3 ,e.sal 4 ,MAX(e.sal) OVER (PARTITION BY e.deptno) emp_salary_max 5 FROM scott.emp e 6 ORDER BY e.deptno, e.empno 7 / DEPTNO EMPNO SAL EMP_SALARY_MAX ------ ----- --------- -------------- 10 7782 2450.00 5000 10 7839 5000.00 5000 10 7934 1300.00 5000 20 7369 800.00 3000 20 7566 2975.00 3000 20 7788 3000.00 3000 20 7876 1100.00 3000 20 7902 3000.00 3000 30 7499 1600.00 2850 30 7521 1250.00 2850 30 7654 1250.00 2850 30 7698 2850.00 2850 30 7844 1500.00 2850 30 7900 950.00 2850
I added the empno field to the query so that you easier can see what happens in the resultset. And, as you can see the result set shows the max salary for the employees in each department…for EACH of the employees in the department.
The equivalent query using the MAX aggregate function would be:
SQL> SELECT e.deptno 2 ,e.empno 3 ,e.sal 4 ,( 5 SELECT ROUND(MAX(e2.sal), 2) 6 FROM scott.emp e2 7 WHERE e2.deptno = e.deptno 8 ) max_dept_salary 9 FROM scott.emp e 10 ORDER BY e.deptno, e.empno 11 / DEPTNO EMPNO SAL MAX_DEPT_SALARY ------ ----- --------- --------------- 10 7782 2450.00 5000 10 7839 5000.00 5000 10 7934 1300.00 5000 20 7369 800.00 3000 20 7566 2975.00 3000 20 7788 3000.00 3000 20 7876 1100.00 3000 20 7902 3000.00 3000 30 7499 1600.00 2850 30 7521 1250.00 2850 30 7654 1250.00 2850 30 7698 2850.00 2850 30 7844 1500.00 2850 30 7900 950.00 2850
Ok, so how can we practically use the MAX analytic funtion?
MAX analytic function, practical example
Let us pretend that the HR department wants to know how many employees in each department that make less than the maximum salary for the department. This is one way you can do this using the MAX analytic function (together with an outer SUM aggregate function):
SQL> SELECT x.deptno 2 ,x.dept_emp_salary_max 3 ,SUM(CASE 4 WHEN x.sal < dept_emp_salary_max THEN 1 5 ELSE 0 6 END) dept_emp_sal_less_max 7 FROM ( 8 SELECT e.deptno 9 ,e.sal 10 ,MAX(e.sal) OVER (PARTITION BY e.deptno) dept_emp_salary_max 11 FROM scott.emp e 12 ) x 13 GROUP BY x.deptno 14 ,x.dept_emp_salary_max 15 / DEPTNO DEPT_EMP_SALARY_MAX DEPT_EMP_SAL_LESS_MAX ------ ------------------- --------------------- 10 5000 2 20 3000 3 30 2850 5
The ROWNUM pseudocolumn
Before we start using the ROW_NUMBER analytic function, let’s look a little bit on how ROWNUM works in a query. ROWNUM is a so-called pseudocolumn. It can be added to any select statement as a field.
Here is a sample of the use of ROWNUM:
SELECT e.deptno
,e.hiredate
,e.empno
,e.ename
,ROWNUM
FROM scott.emp e
Resultset
DEPTNO HIREDATE EMPNO ENAME ROWNUM
20 12/17/1980 7369 SMITH 1
30 2/20/1981 7499 ALLEN 2
30 2/22/1981 7521 WARD 3
20 4/2/1981 7566 JONES 4
30 9/28/1981 7654 MARTIN 5
30 5/1/1981 7698 BLAKE 6
10 6/9/1981 7782 CLARK 7
20 4/19/1987 7788 SCOTT 8
10 11/17/1981 7839 KING 9
30 9/8/1981 7844 TURNER 10
20 5/23/1987 7876 ADAMS 11
30 12/3/1981 7900 JAMES 12
20 12/3/1981 7902 FORD 13
10 1/23/1982 7934 MILLER 14
The ROWNUM field just shows the row number in the resultset that is returned from the query. If you i.e. wanted to only see the first five records in the resultset you could do like this:
SELECT e.deptno ,e.hiredate ,e.empno ,e.ename ,ROWNUM FROM scott.emp e WHERE ROWNUM <= 5
Note that if you want to do an ORDER BY you will need to do an ORDER BY in the query above, and then use ROWNUM in a query that you wrap around the inner query…otherwise the ORDER BY will not work correctly.
The ROW_NUMBER analytic function usage and sample
The ROW_NUMBER analytic function is a bit different than the ROWNUM pseudocolumn. When you use ROWNUM you get the row number based on the WHOLE resultset. When you use the ROW_NUMBER analytical function you will get a row number based on what is in the PARTITION BY and the ORDER BY segments of the expression. Let’s look at a sample.
SELECT e.deptno
,e.hiredate
,e.empno
,e.ename
,ROW_NUMBER() OVER (PARTITION BY e.deptno ORDER BY e.hiredate) rownumber
FROM scott.emp e
Resultset
DEPTNO HIREDATE EMPNO ENAME ROWNUMBER
10 6/9/1981 7782 CLARK 1
10 11/17/1981 7839 KING 2
10 1/23/1982 7934 MILLER 3
20 12/17/1980 7369 SMITH 1
20 4/2/1981 7566 JONES 2
20 12/3/1981 7902 FORD 3
20 4/19/1987 7788 SCOTT 4
20 5/23/1987 7876 ADAMS 5
30 2/20/1981 7499 ALLEN 1
30 2/22/1981 7521 WARD 2
30 5/1/1981 7698 BLAKE 3
30 9/8/1981 7844 TURNER 4
30 9/28/1981 7654 MARTIN 5
30 12/3/1981 7900 JAMES 6
In the above sample the employees are ranged within their department when they were hired. The first hire in the department is number 1, etc.
So, if we only want to see i.e. the first employee hired in each department, we can do like this:
SELECT x.deptno
,x.empno
,x.ename
,x.hiredate
,x.rownumber
FROM (
SELECT e.deptno
,e.hiredate
,e.empno
,e.ename
,ROW_NUMBER() OVER (PARTITION BY e.deptno ORDER BY e.hiredate) rownumber
FROM scott.emp e
) x
WHERE rownumber = 1
Please note that it might be tempting to do this:
SELECT e.deptno
,e.hiredate
,e.empno
,e.ename
,ROW_NUMBER() OVER (PARTITION BY e.deptno ORDER BY e.hiredate) rownumber
FROM scott.emp e
WHERE ROW_NUMBER() OVER (PARTITION BY e.deptno ORDER BY e.hiredate) = 1
But, this will cause the following error: “ORA-30483: window functions are not allowed here”.
Hope this will help you understand a bit how it works.
Pingback: How To Rank Records in Oracle SQL | ORACLETUTSOracleTuts
Pingback: How To Work With Downline Hierarchies In Oracle | ORACLETUTSOracleTuts