In most cases, when working with databases, our interest is the actual records stored in the database. However, an instance may occur where we need to determine the number of records in a specific column or entire table.
To achieve this, we can define a custom function or use the built-in count function. In this guide, we will discuss the count function and various examples of how to use it.
Basic Usage
The syntax for this function is:
Since the count function is an aggregate function, you can pass a wildcard or a specific expression that the function will count. The expression passed to the function as an argument must not return a NULL value.
If an expression returns a NULL value, the function does not count the result.
PostgresSQL Count Function Examples
Let us illustrate how to use the count function using various examples.
Example 1
The first example is the count function with a wildcard. In such an example, Postgres will scan the entire table sequentially and return the number of rows.
An example is:
count
-------
1000
(1 row)
The result above shows that the table contains 100 records.
If you perform the above operation in a table with many records, the query will be slow.
Example 2
Another example is to count the rows in a specific column. You can do this by passing the column name to the count function as:
count
-------
14596
(1 row)
Example 3
We can also use the example above but only return the number of distinct values in the column. For example:
count
-------
19
(1 row)
The above shows 19 unique values in the amount column of the payment table.
Example 4
Suppose you want to get the number of records that meet a specific criterion. In such a case, you can pass a conditional statement to the statement as:
count
-------
700
(1 row)
The query above should return the number of films where the replacement_cost is greater than 15.
Conclusion
This tutorial has taught you how to use the count query to get the number of rows that meet a specific criterion. Remember, experimentation is the key to mastery.
