SQL online course

SQL COUNT Function

Learn SQL COUNT: count rows, non-NULL and distinct values, filter counts with WHERE and HAVING, group results, count joined records, and avoid common errors.

The SQL COUNT function is an aggregate function: it combines information from multiple rows and returns one numeric total. Use it when you need to know how many customers, orders, products, or matching records exist instead of retrieving each individual row.

For background, see SQL functions and the SQL SELECT statement.

COUNT syntax

COUNT(* )
COUNT(expression)
COUNT(DISTINCT expression)

The space in COUNT(* ) is optional and is shown only for readability; the usual spelling is COUNT(*). A column alias gives the calculated result a readable name.

SELECT COUNT(*) AS total_rows
FROM table_name;

COUNT(*) counts rows

COUNT(*) counts every row returned by the query. It counts a row even when one or more of that row's column values are NULL. Without a filter, it counts all rows in the table.

SELECT COUNT(*) AS customer_count
FROM customers;

To count only customers that satisfy a condition, add a WHERE clause:

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed';

WHERE filters individual rows before the count is calculated. Date, status, category, and numeric conditions can all be used.

SELECT COUNT(*) AS recent_orders
FROM orders
WHERE order_date >= '2026-01-01'
  AND total_amount > 100;

COUNT(column_name) and NULL

COUNT(expression) counts only rows for which the expression is not NULL. NULL represents a missing or unknown value; it is not the same as an empty string or zero.

SELECT
    COUNT(*) AS all_customers,
    COUNT(email) AS customers_with_email
FROM customers;

If the table has 1,000 rows but 120 customers have no email value, the results are 1,000 for COUNT(*) and 880 for COUNT(email). The two forms differ because the first counts rows while the second counts populated email values.

COUNT(*) — counts every returned row; NULL values in any column do not matter; use it to count records.

COUNT(column_name) — counts non-NULL results of that expression; use it to count populated values.

COUNT(DISTINCT column_name) — removes duplicate non-NULL values before counting; use it to count unique values.

COUNT(DISTINCT expression)

DISTINCT removes duplicate values before COUNT is applied. NULL values are excluded.

SELECT COUNT(DISTINCT city) AS unique_cities
FROM customers;

If many customers live in the same city, each city contributes only once. Customers whose city is NULL do not contribute to this count. For multiple-column distinct counts, syntax varies between database systems, so check the documentation for your specific database. A portable alternative is often a subquery that first selects distinct combinations.

SELECT COUNT(*) AS unique_customer_locations
FROM (
    SELECT DISTINCT city, country
    FROM customers
    WHERE city IS NOT NULL
) AS locations;

See also SQL SELECT DISTINCT.

Filtering counted rows with WHERE

The logical order is important: WHERE removes nonmatching rows first, and COUNT counts what remains.

SELECT COUNT(*) AS active_customers
FROM customers
WHERE status = 'active';

You cannot ordinarily use an aggregate result in WHERE, because the aggregate has not been computed at that stage. Use HAVING when the condition applies to a group count.

Counting rows within groups

GROUP BY partitions rows into groups and calculates a separate aggregate result for each group.

SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
ORDER BY product_count DESC;

This returns one row per category. Every selected column that is not aggregated generally must appear in GROUP BY.

SELECT department, city, COUNT(*) AS employee_count
FROM employees
GROUP BY department, city;

A grouping column may contain NULL; database systems generally place rows with NULL in one group. An ungrouped aggregate returns one result row, while a grouped query returns one row per group.

Filtering groups with HAVING

HAVING filters groups after aggregate values have been calculated. In contrast, WHERE filters individual source rows before grouping.

WHERE — filters rows; applied before grouping and aggregation; example: count only completed orders.

HAVING — filters groups; applied after aggregation; example: keep categories with at least ten products.

SELECT category_id, COUNT(*) AS product_count
FROM products
GROUP BY category_id
HAVING COUNT(*) >= 10
ORDER BY product_count DESC;

A query can use both clauses: WHERE limits the input rows, and HAVING limits the resulting groups.

SELECT customer_id, COUNT(*) AS completed_order_count
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING COUNT(*) > 3;

For more detail, see SQL WHERE and the related SQL SUM function.

Aliases make count results readable

Use a meaningful column alias such as total_orders, customer_count, or non_null_emails. This is especially useful for reports and application-facing queries.

SELECT COUNT(*) AS total_orders
FROM orders;

Table aliases also make joins shorter and clearer.

COUNT with joins

An INNER JOIN returns only rows with matching records on both sides. For example, this counts orders that have a matching customer:

SELECT COUNT(*) AS orders_with_customers
FROM orders AS o
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id;

A LEFT JOIN retains every row from the left table, including parents with no matching children. To count children correctly, count a non-NULL key from the child table.

SELECT
    c.customer_id,
    c.name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

For a customer with no orders, the left join still produces a result row, but o.order_id is NULL. Therefore COUNT(o.order_id) returns zero.

Do not replace it with COUNT(*) in this pattern:

SELECT c.customer_id, COUNT(*) AS misleading_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id;

COUNT(*) counts the retained customer-side row, so a customer with no orders can incorrectly appear to have a count of one.

INNER JOIN — parents without children are absent; COUNT(*) counts matching joined rows.

LEFT JOIN with COUNT(*) — parents without children remain, but can show one instead of zero.

LEFT JOIN with COUNT(child_primary_key) — parents without children remain and correctly show zero.

Watch for join multiplication

Join cardinality describes how rows relate across tables, such as one-to-many or many-to-many. A one-to-many join can repeat a parent once for every matching child.

SELECT COUNT(*) AS joined_rows
FROM orders AS o
JOIN order_items AS i
    ON i.order_id = o.order_id;

If an order has five line items, it contributes five joined rows. To count unique orders rather than line items, use the entity key:

SELECT COUNT(DISTINCT o.order_id) AS unique_orders
FROM orders AS o
JOIN order_items AS i
    ON i.order_id = o.order_id;

Inspect the join condition and intermediate rows before choosing COUNT(DISTINCT). Distinct counting fixes duplicate entity representation only when unique entities are the actual requirement.

Conditional counting

Conditional aggregation uses conditional logic inside an aggregate to calculate several totals in one result row. The portable pattern is SUM(CASE WHEN ... THEN 1 ELSE 0 END).

SELECT
    SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed_orders,
    SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
    SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders;

Some database systems support a dialect-specific FILTER clause, but the CASE form is broadly portable.

SELECT
    SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_customers,
    SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_customers
FROM customers;

Return values and empty result sets

COUNT returns a number. For an ungrouped query, it returns 0 when no rows qualify:

SELECT COUNT(*) AS matching_orders
FROM orders
WHERE status = 'does-not-exist';

This differs from functions such as SUM and AVG, which may return NULL when no qualifying values exist. A grouped query with no input rows returns no group rows at all, not one group containing zero.

Accuracy and performance

  • Use COUNT(*) when the requirement is to count rows. Do not select a nullable column merely to count records.
  • Use selective WHERE predicates to reduce the rows that must be considered.
  • Indexes can help filtering and joins, although the exact plan depends on the database, table size, statistics, and index design.
  • Validate join cardinality. A count that becomes unexpectedly large after a join often reflects duplicated joined rows.
  • When a result looks wrong, temporarily replace the aggregate with a non-aggregate SELECT and inspect the matching rows.

Common problems and fixes

  • COUNT(column_name) is lower than COUNT(*): the column contains NULL values. Use COUNT(*) for rows or retain the column form when populated values are what you need.
  • A LEFT JOIN reports one child for an empty parent: COUNT(*) counted the retained parent row. Count a non-NULL child key instead.
  • A joined count is too large: one-to-many or many-to-many relationships multiplied rows. Check join conditions and use COUNT(DISTINCT entity_id) only when appropriate.
  • An aggregate in WHERE causes an error: move the aggregate condition to HAVING.
  • COUNT(DISTINCT column_name) is lower than expected: duplicates were collapsed and NULL values were excluded.
  • The count is zero unexpectedly: test the same filters with a regular SELECT; inspect dates, status spelling, data types, NULLs, and join conditions.
  • A grouped query fails: every selected nonaggregate column usually must be included in GROUP BY.

Exam-relevant summary

  • COUNT(*) counts all rows returned, including rows containing NULLs.
  • COUNT(expression) counts only non-NULL expression results.
  • COUNT(DISTINCT expression) counts unique non-NULL values.
  • WHERE filters rows before aggregation; HAVING filters groups after aggregation.
  • GROUP BY produces a separate count for each group.
  • With a LEFT JOIN, count the child primary key to represent no matching children as zero.
  • Joins can multiply rows, so distinguish counting joined rows from counting unique entities.
  • An ungrouped COUNT returns zero when no rows qualify; a grouped query with no input returns no groups.

Reusable COUNT patterns

-- Count all returned rows
SELECT COUNT(*) AS total_rows
FROM table_name;

-- Count non-NULL values
SELECT COUNT(column_name) AS populated_values
FROM table_name;

-- Count unique values
SELECT COUNT(DISTINCT column_name) AS unique_values
FROM table_name;

-- Count filtered rows
SELECT COUNT(*) AS matching_rows
FROM table_name
WHERE condition;

-- Count rows in each group
SELECT group_column, COUNT(*) AS row_count
FROM table_name
GROUP BY group_column;

-- Keep groups meeting a count threshold
SELECT group_column, COUNT(*) AS row_count
FROM table_name
GROUP BY group_column
HAVING COUNT(*) >= minimum_count;

-- Count children while retaining parents with none
SELECT p.parent_id, COUNT(c.child_id) AS child_count
FROM parent_table AS p
LEFT JOIN child_table AS c
    ON c.parent_id = p.parent_id
GROUP BY p.parent_id;

-- Portable conditional count
SELECT SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS conditional_count
FROM table_name;