Introduction:

In this tutorial, we are going to explain how to use Oracle COUNT function. with basic syntax and many examples for better understanding.

COUNT function returns the number of rows returned by the query. We can use this function as an analytic or aggregate.

Syntax:

COUNT({ * OR ALL OR DISTINCT } aggregate_column)

SELECT column1, column2, column3, ... column_n,
       COUNT(aggregate_column)
GROUP BY column1, column2, column3, ... column_n;

Name	Description
COUNT (*)	Function returns the number of rows including null values.
COUNT (ALL)	Function returns the number of rows which is not null.
COUNT (DISTINCT)	Function returns the number of unique values.
aggregate_column	Column or expression that will be counted by count function
column1,column_n	Columns selected with aggregated COUNT function. And we must include them all in GROUP BY clause.
GROUP BY column1	If we use columns along with count(column) in SELECT clause, so we must include them in GROUP BY clause to aggregate the result of COUNT by those columns.

Applies to::

Oracle 19c, Oracle 18c, Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Sample Setup:

DROP TABLE emps PURGE;

CREATE TABLE emps (
    employee_no     NUMBER  CONSTRAINT pk_emps PRIMARY KEY,
    name            VARCHAR2(50),
    salary          NUMBER(10,2),
    comission       NUMBER(10,2),
    manager_no      NUMBER,
    department_no   NUMBER
);

Data Insertion:

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, DEPARTMENT_NO) VALUES ('100', 'Steven', '24000', '90');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, MANAGER_NO) VALUES ('143', 'Layla', '2600', '124');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, COMMISSION, MANAGER_NO, DEPARTMENT_NO) VALUES ('145', 'John', '14000', '5600.00', '100', '80');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, COMMISSION, MANAGER_NO, DEPARTMENT_NO) VALUES ('146', 'Seleem', '13500', '4050.00', '100', '80');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, COMMISSION, MANAGER_NO) VALUES ('161', 'Magy', '7000', '1750.00', '146');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, COMMISSION, MANAGER_NO, DEPARTMENT_NO) VALUES ('170', 'Mark', '10500', '1055.00', '161', '70');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, COMMISSION, MANAGER_NO, DEPARTMENT_NO) VALUES ('175', 'Louise', '12900', '4610.00 ', '100', '60');

INSERT INTO "HR"."EMPS" (EMPLOYEE_NO, NAME, SALARY, COMMISSION, MANAGER_NO, DEPARTMENT_NO) VALUES ('179', 'Rose', '15450', '3010.00', '170', '50');

COMMIT;

Sample Data:

Employee #	Name	Salary	Commission	Manager #	Department #
100	Steven	24,000			90
143	Layla	2,600		124
145	John	14,000	5600.00	100	80
146	Seleem	13,500	4050.00	100	80
161	Magy	7,000	1750.00	146
170	Mark	10,500	1055.00	161	70
175	Louise	12,900	4610.00	100	60
179	Rose	15,450	3010.00	170	50

Examples:

Let’s take some examples about Oracle COUNT and how to use it:

(1) Using COUNT(*) example

SELECT  COUNT(*)
FROM    emps;

(2) Using COUNT with DISTINCT clause example

SELECT  COUNT(DISTINCT DEPARTMENT_NO)
FROM    emps;

(3) Using COUNT with ALL clause example

SELECT  COUNT(ALL commission)
FROM    emps;

(4) Using COUNT in WHERE clause example

SELECT  COUNT(*)
FROM    emps
WHERE   SALARY > 8000;

(5) Using COUNT in GROUP BY clause example

SELECT  department_no,
        COUNT(employee_no)
FROM    emps
GROUP BY department_no;

(6) Using COUNT in GROUP BY clause and eliminating NULL values example

SELECT  department_no,
        COUNT(employee_no)
FROM    emps
WHERE   DEPARTMENT_NO IS NOT NULL
GROUP BY department_no;

(7) Using COUNT in GROUP BY & having clauses example

SELECT  department_no,
        COUNT(employee_no)
FROM    emps
WHERE   DEPARTMENT_NO IS NOT NULL
GROUP BY department_no
HAVING COUNT(EMPLOYEE_NO) > 1;

Similar functions to Oracle COUNT:

SUM : returns the sum of expression or columns.
ROUND : rounds the column, expression, or value to n decimal places.
GREATEST : returns the greatest value of given list of values.
LEAST : returns the smallest value of given list of values.
ABS : returns the absolute value of a number value passed to.

In this tutorial. You have learned how to use the Oracle COUNT function to count the number of rows returned by the query.

We have demonstrated more than 6 examples. To showcase different use of COUNT function in oracle.

Hopefully, it was clear and concise.