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

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!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Abhilash Marichi

Abhilash Marichi

Data Engineer at Amazon. I write about Data, Product & Life.