PostgreSQL COUNT function.

Preamble

The PostgreSQL COUNT function returns the number of expressions.

Syntax of the count function in PostgreSQL

SELECT count(aggregate_expression_id)
FROM tabs
[WHERE conds];

Or the syntax of the count function when grouping results in one or more columns:

SELECT expression1_id,2_id,..._n_id,
count(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY expression1,2,..._n;

Parameters and arguments of the function

• expression1_id, expression2_id,… expression_n_id – Expressions that are not enclosed in the count function and must be included in the GROUP BY operator at the end of the SQL query.
• aggregate_expression_id – This is a column or expression whose non-zero values will be taken into account.
• tabs – These are the tables from which you want to get the records. At least one table must be specified in the FROM operator.
• WHERE conds – Optional. These are the conditions that must be met to select records.

Includes only NOT NULL values

Not everyone understands this, but the count function will only include entries in which the value of the expression in the 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 of a count function that shows how NULL values are evaluated by the count function.
For example, if you have the following table with the name suppliers:

And if you run the next SELECT operator, which 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 next SELECT operator, which uses the count function:

SELECT count(state)
FROM suppls;

--Result: 1

In this example, the count will return only 1 since only one state value in the resulting NOT NULL query set. This will be the first line where state = ‘CA’. This is the only line that is included in the calculation of the count function.

The count function can be used in the following versions of PostgreSQL

PostgreSQL 11, PostgreSQL 10, PostgreSQL 9.6, PostgreSQL 9.5, PostgreSQL 9.4, PostgreSQL 9.3, PostgreSQL 9.2, PostgreSQL 9.1, PostgreSQL 9.0, PostgreSQL 8.4.

Single Expression Example

Let’s look at some examples of count functions to understand how to use the count function in PostgreSQL.

For example, you might want to know the number of entries from the products table that have a product_type ‘Hardware’

SELECT count(*) AS "Number of products"
FROM prods
WHERE prod_type = 'Hardware';

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

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 (departments) where at least one employee earns more than $35000 per year.

SELECT count(DISTINCT depart) AS "Unique departs"
FROM empls
WHERE salary_id > 35000;

Again, the field count(DISTINCT department) has an alias “Unique departments”. This is the name of the field to be displayed in the result set.

Example using GROUP BY

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

For example, you can also use the count function to return records from a department (department name) and “Number of employees” (number of employees in the corresponding department), whose salary is more than $40,000 per year.

SELECT depart, count(*) AS "Number of empls"
FROM empls
WHERE salary_id > 40000
GROUP BY depart;

Since your SELECT operator has one column that is not encapsulated in the count function, you should use the GROUP BY operator. That’s why the department field shall be specified in the GROUP BY operator.

About Enteros

Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.