COUNT Function in Oracle with Examples

Back to: Oracle Tutorials for Beginners and Professionals

COUNT Function in Oracle with Examples

In this article, I am going to discuss the COUNT Function in Oracle with Examples. Please read our previous article where we discussed Multiple Row Functions in Oracle with Examples.

COUNT() Function in Oracle:

The COUNT function in Oracle is used to count the data rows returned in the result set. The Oracle COUNT function counts distinct or all values in data rows returned in a result set. Following are the syntaxes to use the COUNT function in Oracle.

SELECT COUNT(*) FROM tablename;
SELECT COUNT(columnname) FROM tablename;
SELECT COUNT (DISTINCT columnname) FROM tablename;

Examples to Understand COUNT Function in Oracle:

We are going to use the following Employee table to understand the need and use of the COUNT Function in Oracle with Examples.

Please execute the below SQL query to drop the existing Employee table and create a new Employee table with the required sample data.

DROP Table Employee;

CREATE TABLE Employee (
  Id INT PRIMARY KEY,
  Name VARCHAR(15),
  Department VARCHAR(10),
  Salary NUMBER(8, 2),
  Gender VARCHAR(10),
  Age INT,
  City VARCHAR(10)
);

INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1001, 'John', 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1002, 'Smith', 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1003, 'James', 'Finance', 50000, 'Male', 28, 'Delhi');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1004, 'Mike', 'Finance', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1005, 'Linda', 'HR', 75000, 'Female', 26, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1006, NULL, 'IT', 35000, 'Male', 25, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1007, NULL, 'HR', 45000, 'Female', 27, 'Mumbai');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1008, 'John', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1009, 'Smith', 'IT', 50000, 'Male', 28, 'London');
INSERT INTO Employee (Id, Name, Department, Salary, Gender, Age, City) VALUES (1010, 'James', 'HR', 75000, 'Female', 26, 'Mumbai');

Example: Count the Total Number of employees in the Employee table.

Now, let’s count the data rows in the Employee table using the COUNT function in Oracle by executing the below SQL Statement.

SELECT COUNT(*) FROM Employee;

When you execute the above query, you will get the following output. As the Employee table contains 10 rows, so here you will get the output as 10.

COUNT Function with Alias Name in Oracle

We can also use Alias to give the name to the data column. By default, the column name would be COUNT(*) that you can see in the previous example. Instead of COUNT(*), we want to provide the name as Total_Employees. Then we can do the same by using the ALIAS name as shown in the below SQL Statement.

SELECT COUNT(*) AS “Total_Employees” FROM Employee;

Now once you execute the above SQL statement, you will get the following output. Notice, now the column name is Total_Employees instead of COUNT(*).

Count Distinct Values in Oracle

We already know we have 3 distinct values in the Department column of the Employee table. Let us get the number of employees in each department by using the COUNT function. Here, we also need to use the GROUP BY clause to group the employees by department and then apply the COUNT function to each department to get the desired result. Following is the SQL query.

SELECT Department, COUNT(*) AS "Total_Employees"
FROM Employee
GROUP BY Department;

Once you execute the above query, you will get the following output. As you can see, the above SQL statement will get only the distinct values from the column name department and its total number of appearances on the employee table.

Note: The COUNT(*) will count all the rows (duplicates & nulls) in a table.

COUNT(<COLUMN NAME>) in Oracle:

This function is used for counting all values including duplicate values but not null values from a column. As you can see in the Employee table, two employees’ names are NULL and three employees’ names are duplicated. So, let us execute the below SQL statement and pass Name as a parameter to the COUNT function.

SELECT COUNT(NAME) FROM Employee;

Once you execute the above query, you will get the following output. As you can see, we are getting the output as 8. This is because the COUNT(<COLUMN NAME>) ignores the NULL values but includes the duplicate values.

COUNT(DISTINCT <COLUMN NAME>) in Oracle:

This function is used for counting the unique values from a column excluding the NULL values. Here “DISTINCT” keyword is eliminating duplicate values.

SELECT COUNT(DISTINCT NAME) FROM Employee;

Once you execute the above query, you will get the following output. If you look at the employee table we have 5 unique employee names excluding the NULL values and this is the reason why we get the output as 5.

In the next article, I am going to discuss the SUM Function in Oracle with Examples. Here, in this article, I try to explain the COUNT Function in Oracle with Examples and I hope you enjoy this COUNT Function in Oracle with Examples article.