Oracle COUNT function

The Oracle/PLSQL COUNT function  returns the number of rows returned by the query.

Oracle/PLSQL syntax of COUNT function

SELECT COUNT(aggregate_expression_id)
FROM tabs
[WHERE conds]

OR COUNT function syntax with results grouped by one or more columns:

SELECT expression1_id, expression2_id, ... expression_n_id,
COUNT(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY expression1_id, expression2_id, ... expression_n_id;

Parameters and arguments of the function

• expression1_id, expression2id, … expression_n_id – Expressions that are not encapsulated in the COUNT function and must be included in the GROUP BY operator at the end of the SQL sentence.
• aggregate_expression_id – is a column or expression whose non-zero values will be taken into account.
• tabs – tables from which you want to get records. At least one table must be specified in FROM operator.

WHERE conds are optional. These are the conditions that must be met for the selected records.

The COUNT function returns a numeric value.

Includes only NOT NULL values

Not everyone understands this, but the COUNT function will only include entries where the value of the expression COUNT (expression) is NOT NULL. When an expression contains a value of NULL, it is not included in the COUNT calculation.

Let’s look at an example function COUNT, which demonstrates how NULL values are evaluated by function COUNT.

For example, if you have the following table named suppls:

And if you run the next SELECT operator that uses the COUNT function:

SELECT COUNT(suppl_id)
FROM suppls;

-Result: 3

This example COUNT will return 3 because all supplier_id values in the resulting query set are not equal to NULL.

However, if you run the following SELECT operator which uses COUNT:

SELECT COUNT(state_id)
FROM suppls;

-Result: 1

This example COUNT will return only 1 since only one state value in the query result set is NOT NULL. This will be the first row where state = ‘CA’. This is the only line that is included in the COUNT calculation.

The COUNT function can be used in the following versions of Oracle/PLSQL

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

Example – with one field

Consider some examples of the Oracle COUNT function to understand how to use the COUNT function in Oracle/PLSQL.

For example, you can find out how many employees have salaries above $57,000 per year.

SELECT COUNT(*) AS "Number of empls"
FROM empls
WHERE salary_id > 57,000;

In this example of the COUNT function, we called the expression COUNT (*) as “Number of employees”. As a result, “Number of employees” will be displayed as a field name when returning the result set.

Example – using DISTINCT

You can use the DISTINCT operator in the COUNT function. For example, the following SQL statement returns the number of unique departments where at least one employee earns more than $48,000 per year.

SELECT COUNT(DISTINCT depart) AS "Unique departs"
FROM empls
WHERE salary_id > 48,000;

Again, the field COUNT (DISTINCT department) is called “Unique departments”. This is the name of the field that will be displayed in the result set.

Example – using GROUP BY

In some cases you will need to use the GROUP BY operator with the COUNT function.

For example, you can also use the COUNT function to return the name of the department and the number of employees in the corresponding department that are in state ‘CA’.

SELECT depart, COUNT(*) AS "Number of empls"
FROM empls
WHERE state = 'CA'
GROUP BY depart;

Since your SELECT operator has one column that is not encapsulated in the COUNT function, you must use the GROUP BY operator. Therefore, the department field must be specified in the GROUP BY operator.

Using COUNT in Other Ways (Introduction to Oracle SQL)