Oracle COUNT Function: From Basics to Advanced

Oracle is one of the most widely used database management systems for a reason. It provides an extensive set of tools that allow you to accomplish even the most intricate tasks. Today, we will focus on the Oracle COUNT function, exploring what it is, how it works, its structure, and addressing common misconceptions and best practices. dbForge Studio for Oracle will serve as our trusted wingman, supporting us throughout this process.

Contents

• Understanding the COUNT function
• COUNT function variants
• Practical usage of the COUNT function in Oracle
• COUNT with different clauses
• Common misconceptions
• Best practices and tips
• Conclusion

 

Understanding the COUNT function

Our first stop will traditionally be the essentials: the definition and basic syntax.

The Oracle COUNT function counts the number of rows in a table or the number of times a specific value appears. It provides a way to find out how many items meet certain conditions in your data.

The syntax of the function will look somewhat like this:

Note: Even though this article focuses on Oracle, you can also find information about the COUNT function in MySQL and the COUNT function in SQL Server on our blog.

COUNT function variants

Let’s break down the syntax into smaller parts:

COUNT({ * | [ DISTINCT | ALL ] expression }) [ OVER (analytic_clause) ]

COUNT accepts a clause which can be either ALL, DISTINCT, or *:

• * returns the number of items in a group, including NULL and duplicate values.
• DISTINCT expression returns the number of unique and NOT NULL items in a group.
• ALL expression evaluates the expression and returns the number of NOT NULL items in a group, including duplicate values.

If you do not explicitly specify the parameter, the function uses ALL by default.

Note: Unlike other aggregate functions, such as AVG or SUM, COUNT does not ignore NULL values.

Practical usage of the COUNT function in Oracle

Now that the express tour through the theoretical part of the article is over, it’s time for the fun: how do we apply the COUNT function in the real world? In this article, we will be using dbForge Studio for Oracle — a powerful integrated development environment (IDE) that helps Oracle developers increase their PL/SQL coding speed and provides versatile data editing tools for managing in-database and external data.

With dbForge Studio for Oracle, you benefit from many features tailored specifically to streamline your workflow. The smart PL/SQL Formatter ensures code readability and adherence to best practices, while code completion expedites the coding process by suggesting context-sensitive options. Code snippets provide reusable code templates, simplifying the creation of common code structures, and the SQL Editor offers a comprehensive environment for writing, testing, and debugging SQL queries. These features collectively enhance productivity, reduce development time, and facilitate seamless database management.

Basic usage

Before we begin, we need to prepare the playground for our experiments by creating a new films table and filling it with some test data:

CREATE TABLE films(val VARCHAR2(50));
INSERT INTO films(val) VALUES('The First Film');
INSERT INTO films(val) VALUES('The First Film');
INSERT INTO films(val) VALUES('The Second Film');
INSERT INTO films(val) VALUES(NULL);
INSERT INTO films(val) VALUES('The Third Film');
INSERT INTO films(val) VALUES(NULL);
INSERT INTO films(val) VALUES('The Fourth Film');

To see the results of our hard work, make sure to execute the following SELECT statement:

SELECT * FROM films;

Now, let’s count all the rows in the films table as an example of basic COUNT usage:

SELECT COUNT(*) FROM films;

As expected, we got 7 as a result since there are 7 rows in the said table.

Now, let’s move on to counting NOT NULL values:

SELECT COUNT(ALL val) FROM films;

Here, you will see 5 in the output as there were only 2 NULLs out of 7 rows in the films table.

Advanced usage

Moving on to the examples that are a bit more complicated than the previous one. In this section, we are going to explore how COUNT behaves both as an aggregate and analytic function.

Aggregate usage

When we were talking about COUNT basic usage, you already saw a couple of examples of how it operates as an aggregate function, where multiple values are processed together to form a single summary statistic. It allows you to count the total of rows in your table, the number of NULL, NOT NULL, unique values, and so on. However, there are more applications for COUNT out there.

Analytic usage

Let’s say you want to count how many times each film title appears in the table, but instead of grouping the results, you want to keep the original order of the rows and include the count alongside each row.

SELECT val,
       COUNT(val) OVER (PARTITION BY val ORDER BY val) AS FILM_COUNT
  FROM films
  ORDER BY val;

In the query above:

• PARTITION BY divides the result set into partitions. Each partition contains rows with the same film title.
• COUNT is applied within each partition.
• ORDER BY orders the rows within each partition. However, in this case, it doesn’t affect the counting since all rows in a partition are identical.
• FILM_COUNT shows how many times each film title appears in the table, but without aggregating or collapsing the rows.

COUNT with different clauses

This section of today’s article explores how the COUNT function interacts with the DISTINCT, WHERE, JOIN, and HAVING clauses and how to group the results we get from all those manipulations.

COUNT with DISTINCT

For this example, we are adding the DISTINCT clause to the equation:

SELECT COUNT( DISTINCT val ) FROM films;

The result would be 4, as there are two NULL rows in the table, and the name of one film is duplicated.

COUNT with WHERE

The WHERE clause, used along with COUNT, ensures that only rows with NULL values are counted.

SELECT COUNT(*) AS EMPTY_FILMS
  FROM films
  WHERE VAL IS NULL;

COUNT with JOIN

We need another table besides films to demonstrate how COUNT and JOIN work together. Let it be actor:

SELECT F.VAL,
       COUNT(A.ACTOR_ID) AS ACTORS_COUNT
  FROM FILMS F
    LEFT JOIN ACTOR A
      ON F.VAL = A.film_title
  GROUP BY F.VAL;

This query counts the number of actors associated with each film title, using a LEFT JOIN to include all films, even those without any associated actors.

Grouping COUNT results with HAVING

The HAVING clause is used to filter groups after they have been formed with GROUP BY. It allows you to apply a condition to the aggregate results.

SELECT VAL,
       COUNT(*) AS COUNT_PER_FILM
  FROM FILMS
  GROUP BY VAL
  HAVING COUNT(*) > 1;

The films are grouped by title, and the occurrences of each one are counted. The HAVING COUNT(*) > 1 clause filters the results to only include film titles that appear more than once.

Common misconceptions

Misconceptions about the COUNT function in Oracle are surprisingly common and can lead to inefficient queries and inaccurate results. To avoid these pitfalls, it’s essential to understand and address the most prevalent misunderstandings, such as:

Misconception	Reality	Example
COUNT(column_name) includes NULLs	COUNT(column_name) only counts rows where the specified column is not NULL. A common mistake is expecting it to count all rows, including those with NULL.	If you have a table with 7 rows and 2 rows contain NULL in the specified column, the query will return 5, not 7.
COUNT(1) is faster than COUNT(*)	A popular myth suggests that COUNT(1) is faster than COUNT(*), assuming COUNT(1) counts a constant value (1) rather than all columns. There’s no performance difference between the two because Oracle optimizes both the same way.	Whether you use COUNT(1) or COUNT(*), Oracle performs the same internal row-counting operation, so choose based on readability rather than performance.
COUNT(*) and COUNT(column_name) are interchangeable	COUNT(*) counts all rows, regardless of NULL values in any column, while COUNT(column_name) only counts rows where column_name is not NULL.	If a column has NULL values, COUNT(column_name) will yield a lower count than COUNT(*).
COUNT(DISTINCT column_name) is always efficient	Using COUNT(DISTINCT column_name) can be resource-intensive, particularly on large datasets, as it requires sorting or hashing to remove duplicates before counting. This operation can be slow without proper indexing or when used excessively.	For large datasets, using COUNT(DISTINCT column_name) without indexes on the column can lead to performance degradation. Consider using approximate functions like APPROX_COUNT_DISTINCT for quicker results when exact precision is not necessary.
COUNT ignores performance considerations	When used with complex JOINs or GROUP BY clauses, COUNT can lead to slow query performance if not optimized properly with indexing and careful query structuring. Many users assume that simply adding COUNT won’t significantly impact performance, which isn’t always true.	Counting results from a large, joined dataset without indexes can result in long execution times. Ensuring that join columns and grouping keys are indexed is crucial for maintaining performance.

Best practices and tips

Keeping in mind the misconceptions we mentioned in the previous section, we derived some tips for you to avoid those and be a few steps closer to perfecting your Oracle database:

1. Don’t fall for the myth that COUNT(1) is inherently faster than COUNT(*). Oracle treats them the same.
2. Use DISTINCT judiciously and be aware of its potential performance impacts on large datasets.
3. Be mindful of how JOINs and groupings can affect performance, and optimize with indexes where possible.
4. Always test and validate the behavior of COUNT in your queries to avoid unexpected results or performance issues.
5. Understand how COUNT handles NULL values, especially when using specific columns versus counting all rows.

Conclusion

The Oracle COUNT function is a versatile tool capable of functioning as an aggregate and analytical function. This article discussed its practical applications and how it interacts with various clauses, such as DISTINCT, WHERE, JOIN, and HAVING. We have also provided tips and best practices on how to handle some common misconceptions. For all these tasks and more, dbForge Studio for Oracle is an invaluable asset. It offers a robust set of tools to streamline your database routine. Experience its power firsthand by downloading a free 30-day trial and see how it can elevate your Oracle database management to the next level.