Oracle COUNT Function - Counting the Records in a Table

Oracle COUNT() function is used to get a quick count of the number of records in the table or the number of values for a column in the table.

The syntax of the COUNT function is as follows:

SELECT COUNT(*) FROM TABLE_NAME;

SELECT COUNT(<column-name>) FROM TABLE_NAME;

The COUNT(*) function returns the number of rows in a result set returned by a SELECT statement.

The COUNT(<column-name>) function returns number of records present in the column from result set returned by a SELECT statement .

The COUNT(*) returns the number of rows including duplicate, non-NULL and NULL rows. If you want to count only the unique values that show up within a table, you would use the DISTINCT syntax within the COUNT function. We cannot use the DISTINCT command with COUNT(*), only with COUNT (<column-name>).

SELECT COUNT(DISTINCT ITEM_ID) FROM ITEMS;

Oracle COUNT Function Examples

Creating table for demonstrating COUNT function

CREATE TABLE EMP_SALARY_COMMISION

(EMP_ID NUMBER(4) ,

SALARY NUMBER(7,2),

COMM NUMBER(7,2)

);

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('100', '1000', '110');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('101', '1000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('102', '1000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('103', '1000', '20');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('104', '2000', '100');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('105', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('106', '2000');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('107', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY, COMM) VALUES ('108', '3000', '60');

INSERT INTO EMP_SALARY_COMMISION (EMP_ID, SALARY) VALUES ('109', '3000');

COMMIT;

Counts all rows including NULL

SELECT COUNT(*) FROM EMP_SALARY_COMMISION ----------- 10

Counts all rows excluding NULL

SELECT COUNT(COMM) FROM EMP_SALARY_COMMISION ----------- 6

SELECT COUNT(ALL COMM) FROM EMP_SALARY_COMMISION ----------- 6

Counts only the distinct rows (Excluding NULL, Duplicates are considered once)

SELECT COUNT(DISTINCT COMM) FROM EMP_SALARY_COMMISION ----------- 4

Adding alias to count value

SELECT COUNT(JOB) AS NO_OF_MANAGERS FROM EMP WHERE JOB='MANAGER'

All Chapters