8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
COUNT Analytic Function
This article gives an overview of the COUNT analytic function introduced in Oracle database 21c. If you are new to analytic functions you should probably read this introduction to analytic functions first.
Related articles.
Setup
The examples in this article require the following table.
--drop table emp purge;
create table emp (
empno number(4) constraint pk_emp primary key,
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
insert into emp values (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
insert into emp values (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
insert into emp values (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
insert into emp values (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
insert into emp values (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
insert into emp values (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
insert into emp values (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
insert into emp values (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
insert into emp values (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
insert into emp values (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
insert into emp values (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
insert into emp values (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
insert into emp values (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
insert into emp values (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
commit;
COUNT as an Aggregate Function
The COUNT aggregate function returns the number of rows in a set. As an aggregate function it reduces the number of rows, hence the term "aggregate". If the data isn't grouped we turn the 14 rows in the EMP table to a single row with the aggregated values. Using "*" or a mandatory column as a parameter returns the total number of rows in the set. Using an optional column returns the total number of rows with a non-null value in that column.
select count(*) as count_total,
count(sal) as count_sal,
count(comm) as count_comm
from emp;
COUNT_TOTAL COUNT_SAL COUNT_COMM
----------- ---------- ----------
14 14 4
SQL>
We can get more granularity of information by including a GROUP BY clause. In the following example we see the minimum and maximum values on a per-department basis.
select deptno,
count(*) as count_total,
count(sal) as count_sal,
count(comm) as count_comm
from emp
group by deptno
order by deptno;
DEPTNO COUNT_TOTAL COUNT_SAL COUNT_COMM
---------- ----------- ---------- ----------
10 3 3 0
20 5 5 0
30 6 6 4
SQL>
In both cases we have aggregated the data to get the values, returning less rows than we started with. Analytic functions allow us to return these aggregate values while retaining the original row data.
COUNT Analytic Function
The basic description for the COUNT analytic function is shown below. The analytic clause is described in more detail here.
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
Omitting a partitioning clause from the OVER clause means the whole result set is treated as a single partition. In the following example we display the number of employees, as well as all the original data.
select empno,
ename,
deptno,
sal,
count(*) over () as amount
from emp;
EMPNO ENAME DEPTNO SAL AMOUNT
---------- ---------- ---------- ---------- ----------
7369 SMITH 20 800 14
7499 ALLEN 30 1600 14
7521 WARD 30 1250 14
7566 JONES 20 2975 14
7654 MARTIN 30 1250 14
7698 BLAKE 30 2850 14
7782 CLARK 10 2450 14
7788 SCOTT 20 3000 14
7839 KING 10 5000 14
7844 TURNER 30 1500 14
7876 ADAMS 20 1100 14
7900 JAMES 30 950 14
7902 FORD 20 3000 14
7934 MILLER 10 1300 14
SQL>
Adding the partitioning clause allows us to return the count within a partition.
select empno,
ename,
deptno,
sal,
count(*) over (partition by deptno) as amount_by_dept
from emp;
EMPNO ENAME DEPTNO SAL AMOUNT_BY_DEPT
---------- ---------- ---------- ---------- --------------
7782 CLARK 10 2450 3
7839 KING 10 5000 3
7934 MILLER 10 1300 3
7566 JONES 20 2975 5
7902 FORD 20 3000 5
7876 ADAMS 20 1100 5
7369 SMITH 20 800 5
7788 SCOTT 20 3000 5
7521 WARD 30 1250 6
7844 TURNER 30 1500 6
7499 ALLEN 30 1600 6
7900 JAMES 30 950 6
7698 BLAKE 30 2850 6
7654 MARTIN 30 1250 6
SQL>
Quick Links
The "*" indicates the function supports the full analytic syntax, including the windowing clause.
For more information see:
Hope this helps. Regards Tim...