5 most common ways of using count() function in Oracle

Photo by olia danilevich from Pexels

For the examples provided below, I am considering this simple table

CREATE TABLE TEMP_EMPLOYEE (
ID INT
,NAME VARCHAR(255)
,LOCATION VARCHAR(255)
);

With values as shown below —

Count(*)

This gives the count of records in the table.

Count(1)

This also gives the count of records in the table

Count(column_name)

This gives the count of NOT NULL records.

Let’s use count(ID), if you see the records, ID is populated for all the records so the count would be 4

But, if we use count(LOCATION), the count would be 3 because there is one record with value NULL in this column

Let’s add 3 more records into our table now, just to play around more!

Count(distinct column_name)

This will count the number of distinct values in the specified column

Count() with a group by

We can always use count() examples provided above with a group by clause.

The example provides the number of people live in each location.

These are the basic usages, I hope you enjoyed learning!