Count Analytical Function In Oracle

Count Analytical Function In Oracle | In this post we will learn the Oracle analytic count() function. What is the count() analytical function In the Oracle database, what is its syntax, and how can we use them and their examples?

We will use the EMP table to demonstrate the examples. See more:- EMP DEPT Tables Queries.

What is Count Analytical Function in Oracle?

The count is an aggregate function, In order to analyze data across multiple rows and deliver the results in the current row, analytical functions are utilized.

Syntax of count() analytical function in Oracle:-
COUNT( [ALL|DISTINCT|*] expression)

The clause that can be accepted by the COUNT() function is one of ALL, DISTINCT, or *;

Examples of Count Analytical Function in Oracle

1) When using COUNT(ALL expression), we can get the total number of non-null elements in a group, including duplicate values, after evaluating the expression.

Display the number of rows in the emp table using the count analytical function.
To find the total number of rows we can use count(*) or we can pass any column name which doesn’t contain a null value in the count() analytical function.

SELECT count(*) FROM emp;

Output:-

COUNT(*)
--------
14

SELECT count(empno) FROM emp;

Output:-

COUNT(*)
--------
14

The empno column in the emp table doesn’t contain any null value therefore the coun(*) and count(empno) gives the same result. But the comm column in the emp table contains many nulls and the count() analytic function excludes them. Therefore to find the total number of rows in the table it is better to use coun(*) instead of count(column_name).

SELECT count(comm) FROM emp;

Output:-

COUNT(*)
--------
4

The number of non-null rows in the items table, taking into account duplicate rows, is returned by the following statement’s use of the COUNT(ALL order_id) function.

2) The function COUNT(DISTINCT expression) returns the total number of distinct, non-null entries in a group.

The COUNT(DISTINCT order_id) function is used to only return the number of distinct, non-null rows from the items table in the following statement:

SELECT count(DISTINCT job) FROM emp;

Output:-

COUNT(DISTINCTJOB)
-----------------
5

The below query can show us the unique jobs of the emp table.

SELECT DISTINCT job FROM emp;

Output:-

JOB
----------
ANALYST
CLERK
SALESMAN
MANAGER
PRESIDENT

3) The COUNT(*) function, which includes duplicate and NULL values, returns the total number of elements in a group.

The COUNT(*) function is used in the sentence that follows to retrieve the total number of rows in the emp table based on the job, including duplicate and NULL entries.

Write a SQL query to display the number of employees in each job from the emp table.

SELECT job, count(*) FROM emp
GROUP BY job;

Output:-

JOB        COUNT(*)
---------  --------
ANALYST          2
CLERK            4
SALESMAN         4
MANAGER          3
PRESIDENT        1

The COUNT() function defaults to using ALL if DISTINCT or ALL are not explicitly specified. Let us explore some more examples with the analytical functions:-

Count Analytical Function In Oracle with WHERE clause

If we want to find the number of employees whose name starts with the ‘J’ character then we need to use the WHERE clause along with the like operator:-

SELECT count(*) 
FROM emp
WHERE ename LIKE 'J%'; 

Output:-

COUNT(*)
--------
2

Write a SQL query to find the number of employees working in the job “CLERK” from the emp table.

SELECT count(*) 
FROM emp
WHERE job='CLERK'; 

Output:-

COUNT(*)
--------
4

Count Analytical Function In Oracle with GROUP BY clause

We have already seen an example of it in the query to display the number of employees in each job from the emp table. Let us see another example.

Find the number of employees in each department (deptno)?
The function COUNT(*) returns the number of employees for every department.

SELECT deptno, count(*) 
FROM emp
GROUP BY deptno
ORDER BY deptno desc;

Output:-

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

Oracle Analytic count Function with OVER clause

We can see the count of employees present in each department with complete details

SELECT empno, ename, deptno, count(*)
OVER (partition BY deptno) AS employees_by_dept
FROM emp;

Output:-

EMPNO  ENAME	DEPTNO	EMPLOYEES_BY_DEPT
-----  ------- ------- -----------------
7782	CLARK	    10                 3
7934	MILLER	    10                 3
7839	KING	    10                 3
7902	FORD	    20                 5
7788	SCOTT	    20                 5
7566	JONES	    20                 5
7369	SMITH	    20                 5
7876	ADAMS	    20                 5
7521	WARD	    30                 6
7654	MARTIN	    30                 6
7844	TURNER	    30                 6
7900	JAMES	    30                 6
7499	ALLEN	    30                 6
7698	BLAKE	    30                 6

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search