PostgreSQL COUNT

PostgreSQL COUNT() Function

The COUNT() function is an incredibly vital aggregate function in PostgreSQL. It cleanly returns the total number of rows that match a specified criteria.

It does not return the actual data inside the rows themselves. It simply tallies them up and outputs a single numeric integer representing the total.

Counting All Rows

The most common usage of this function is counting every single row in a table. You achieve this by placing an asterisk (*) inside the function parenthesis.

Using COUNT(*) counts absolutely everything, including rows that contain NULL values. It is heavily optimized and executes extremely fast on standard database engines.

-- Retrieve the total number of registered products
SELECT COUNT(*) AS TotalProducts
FROM Products;

Counting Specific Columns

Instead of an asterisk, you can pass a specific column name into the function. Using COUNT(column_name) behaves slightly differently than counting everything.

It strictly counts only the rows where the specified column is not NULL. This is useful for identifying exactly how many users have provided a phone number.

-- Count only customers who have an actual ContactName listed
SELECT COUNT(ContactName) AS CustomersWithContacts
FROM Customers;

Counting Distinct Values

Sometimes you want to find the amount of unique entries within a column. You can pair the DISTINCT keyword tightly inside the COUNT() function.

COUNT(DISTINCT column_name) will aggressively ignore all duplicate entries. It provides an exact total of how many different categories or regions exist.

-- Find out exactly how many different countries are represented
SELECT COUNT(DISTINCT Country) AS UniqueCountries
FROM Customers;

Combining COUNT with WHERE

You frequently need to count subsets of data rather than the entire table. Adding a WHERE clause allows you to count rows that meet strict requirements.

This easily calculates metrics like the total number of currently active users. The database securely filters the data first, and then tallies the remainder.

Summary

The COUNT() function is mandatory for building functional administrative dashboards. Use COUNT(*) for total table size, and COUNT(column) to ignore missing data.

Always combine it with DISTINCT when searching for unique categorical totals.