Oracle Aggregate Function - Oracle/PLSQL COUNT Function

This Oracle tutorial explains how to use the Oracle/PLSQL COUNT function.

Count function operate on a group of rows and return one row of output.

You can use count function with any valid expression. For example, you can use the COUNT() function with numbers, strings, and datetimes.

Null values are ignored by count function.

You can use the DISTINCT keyword to exclude duplicate entries.

Syntax

The syntax for the Oracle/PLSQL COUNT function is:

SELECT COUNT( expression )
 FROM tables
 WHERE conditions;

expression can be a field or formula.

Example


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
--   w w w  .j  av a  2s  .c o  m
INSERT INTO EMP VALUES (1, 'SMITH', 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, 'ALLEN', 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'JONES', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL>
SQL> select count(empno) from emp;

COUNT(EMPNO)
------------
          11

SQL>

ROWID with Count

Using ROWID with Count


SQL>
SQL> select count(rowid) from emp;

COUNT(ROWID)
------------
          11

SQL>

Count with Group By

Using Count function with group by clause:


SQL>--   w w w  . j a  v  a 2s  .c om
SQL>
SQL> select count(rowid) from emp group by deptno;

COUNT(ROWID)
------------
           5
           4
           2

SQL>

Count with DISTINCT and ALL


CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
                  ENAME VARCHAR2(10),
                  JOB VARCHAR2(9),
                  SAL NUMBER(7, 2),
                  DEPTNO NUMBER(2));
-- from   w w w .  j  av a  2  s .  c  om
INSERT INTO EMP VALUES (1, NULL, 'CLERK',     800,    20);
INSERT INTO EMP VALUES (2, NULL, 'SALESMAN', 1600,    30);
INSERT INTO EMP VALUES (3, 'WARD',  'SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (4, 'WARD', 'MANAGER',  2975,    20);
INSERT INTO EMP VALUES (5, 'MARTIN','SALESMAN', 1250,    30);
INSERT INTO EMP VALUES (6, 'BLAKE', 'MANAGER',  2850,    30);
INSERT INTO EMP VALUES (7, 'CLARK', 'MANAGER',  2850,    10);
INSERT INTO EMP VALUES (8, 'SCOTT', 'ANALYST',  3000,    20);
INSERT INTO EMP VALUES (9, 'KING',  'PRESIDENT',3000,    10);
INSERT INTO EMP VALUES (10,'TURNER','SALESMAN', 1500,    30);
INSERT INTO EMP VALUES (11,'ADAMS', 'CLERK',    1500,    20);

SQL> select count(ename), count(distinct ename), count(all ename) from emp;

COUNT(ENAME) COUNT(DISTINCTENAME) COUNT(ALLENAME)
------------ -------------------- ---------------
           9                    8               9

SQL>