PostgreSQL - COUNT Function

PostgreSQL - The COUNT Function

In PostgreSQL, COUNT() is a fundamental aggregate function used to count the number of rows that match a specified condition. It’s essential for data analysis, reporting, and application logic. This updated tutorial explores its various forms and best practices.

Setup: Creating a Sample Table

To demonstrate the COUNT() function, let’s work with a modern employees table. This table includes different data types and some NULL values, which are important for understanding how COUNT behaves.

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary NUMERIC(10, 2),
    hire_date DATE NOT NULL
);

INSERT INTO employees (first_name, last_name, department, salary, hire_date) VALUES
('Alice', 'Johnson', 'Engineering', 90000.00, '2022-01-15'),
('Bob', 'Smith', 'Engineering', 95000.00, '2021-03-10'),
('Charlie', 'Brown', 'HR', 65000.00, '2023-05-20'),
('Diana', 'Prince', 'Sales', 80000.00, '2022-08-01'),
('Edward', 'King', 'Sales', 82000.00, '2022-09-11'),
('Fiona', 'Glenanne', 'HR', NULL, '2023-01-05'),  -- Note the NULL salary
('George', 'Costanza', 'Engineering', 105000.00, '2020-11-30');

Core Usage of COUNT()

1. Counting All Rows: COUNT(*)

The most common use of COUNT is to count every single row in a table or result set. COUNT(*) is the standard and most performant way to achieve this.

SELECT COUNT(*) FROM employees;

Result:

 count
-------
     7
(1 row)

2. Counting Rows with a Condition

You can combine COUNT(*) with a WHERE clause to count only the rows that meet specific criteria. For example, let’s count the number of employees in the ‘Engineering’ department.

SELECT COUNT(*) FROM employees WHERE department = 'Engineering';

Result:

 count
-------
     3
(1 row)

Advanced COUNT Techniques

3. Counting Non-Null Values: COUNT(column_name)

When you specify a column name inside COUNT(), it only counts the rows where that specific column has a non-NULL value. This is a crucial distinction from COUNT(*). Let’s count how many employees have a salary recorded.

SELECT COUNT(salary) FROM employees;

Result: (Note that Fiona Glenanne’s NULL salary is not counted)

 count
-------
     6
(1 row)

4. Counting Unique Values: COUNT(DISTINCT column_name)

To count the number of unique, non-NULL values in a column, use the DISTINCT keyword. For example, let’s find out how many different departments exist in our company.

SELECT COUNT(DISTINCT department) FROM employees;

Result:

 count
-------
     3
(1 row)

5. Counting with Grouping: COUNT and GROUP BY

A powerful and common pattern is to use COUNT() with GROUP BY to create summary reports. Let’s count the number of employees in each department.

SELECT
    department,
    COUNT(*) AS number_of_employees
FROM
    employees
GROUP BY
    department
ORDER BY
    number_of_employees DESC;

Result:

 department  | number_of_employees
-------------+-----------------------
 Engineering |                     3
 HR          |                     2
 Sales       |                     2
(3 rows)

Best Practices and Common Pitfalls

• Performance: COUNT(*) is generally the fastest way to count all rows. PostgreSQL is highly optimized for this operation. COUNT(1) is equivalent to COUNT(*) and offers no performance benefit.
• Clarity is Key: Use aliases (e.g., AS number_of_employees) to make your results more readable, especially in reports and applications.
• Understanding NULLs: The most common mistake is confusing COUNT(*) with COUNT(column). Always remember that COUNT(column) ignores NULLs. This is a feature, not a bug, and is very useful for data quality checks.
• Large Tables: Counting rows in very large tables can be resource-intensive. For estimations, PostgreSQL provides statistical approximations that can be much faster. For exact counts, ensure your queries are well-indexed if using a WHERE clause.