Oracle COUNT

Summary: in this tutorial, you will learn how to use the Oracle COUNT() function to get the number of items in a group.

Oracle COUNT() function syntax

The Oracle COUNT() function is an aggregate function that returns the number of items in a group.

The syntax of the COUNT() function is as follows:

COUNT( [ALL | DISTINCT | * ] expression)
Code language: SQL (Structured Query Language) (sql)

The COUNT() function accepts a clause which can be either ALL, DISTINCT, or *:

  • COUNT(*) function returns the number of items in a group, including NULL and duplicate values.
  • COUNT(DISTINCT expression) function returns the number of unique and non-null items in a group.
  • COUNT(ALL expression) evaluates the expression and returns the number of non-null items in a group, including duplicate values.

If you don’t explicitly specify DISTINCT or ALL, the COUNT() function uses the ALL by default.

Note that, unlike other aggregate functions such as AVG() and SUM(), the COUNT(*) function does not ignore NULL values.

Oracle COUNT() examples

Let’s take some examples of using the COUNT() function.

A) COUNT(*) vs. COUNT(DISTINCT expr) vs. COUNT(ALL)

Let’s create a table named items that consists of a val column and insert some sample data into the table for the demonstration.

CREATE TABLE items(val number); INSERT INTO items(val) VALUES(1); INSERT INTO items(val) VALUES(1); INSERT INTO items(val) VALUES(2); INSERT INTO items(val) VALUES(3); INSERT INTO items(val) VALUES(NULL); INSERT INTO items(val) VALUES(4); INSERT INTO items(val) VALUES(NULL); SELECT * FROM items;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - sample table

The following statement uses the COUNT(*) function to return the number of rows in the items table including NULL and duplicate values:

SELECT COUNT(*) FROM items;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - star example

The following statement uses the COUNT(DISTINCT val) to return only the number of distinct and non-null rows from the items table:

SELECT COUNT( DISTINCT val ) FROM items;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - DISTINCT example

The following statement uses the COUNT(ALL val) function to return the number of non-null rows in the items table, considering duplicates.

SELECT COUNT( ALL val ) FROM items;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - ALL example

B) Simple Oracle COUNT() example

The following example returns the number of rows in the products table:

SELECT COUNT(*) FROM products;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - count products

C) Oracle COUNT() with WHERE clause example

If you want to find the number of products in the category id 1, you can add a WHERE clause to the query above:

SELECT COUNT(*) FROM products WHERE category_id = 1;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - with WHERE clause example

D) Oracle COUNT() with GROUP BY clause example

To find the number of products in each product category, you use the following statement:

SELECT category_id, COUNT(*) FROM products GROUP BY category_id ORDER BY category_id;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - with GROUP BY example

In this example,

  • First, the GROUP BY clause divides the products into groups based on the product category (category_id).
  • Second, the COUNT(*) function returns the number of products for each group.

E) Oracle COUNT() with LEFT JOIN clause

The following examples get all category names and the number of products in each category by joining the product_categories with the products table and using the COUNT() function with the GROUP BY clause.

SELECT category_name, COUNT( product_id ) FROM product_categories LEFT JOIN products USING(category_id) GROUP BY category_name ORDER BY category_name;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - with LEFT JOIN example

F) Oracle COUNT() with HAVING clause example

The following statement retrieves category names and the number of products in each. In addition, it uses a HAVING clause to return the only category whose the number of products is greater than 50.

SELECT category_name, COUNT( product_id ) FROM product_categories LEFT JOIN products USING(category_id) GROUP BY category_name HAVING COUNT( product_id ) > 50 ORDER BY category_name;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - with HAVING example

G) Using Oracle COUNT() and HAVING clause to find duplicate values

You can use the COUNT() function and a HAVING clause to find rows with duplicate values in a specified column.

For example, the following statement returns the contacts’ last names that appear more than one:

SELECT last_name, COUNT( last_name ) FROM contacts GROUP BY last_name HAVING COUNT( last_name )> 1 ORDER BY last_name;
Code language: SQL (Structured Query Language) (sql)
Oracle COUNT - find duplicate values

In this statement:

  • Firstly, the GROUP BY clause divides the rows in the contacts table into groups based on the values in the last_name column.
  • Secondly, the COUNT() function returns the number of the same last names for each last name.
  • Finally, the HAVING clause returns only groups that have more than one value of the last name.

In this tutorial, you have learned how to use the Oracle COUNT() function to return the number of items in a group.

Was this tutorial helpful?