SQL SELECT COUNT: Syntax, NULL Handling, Performance, and Examples

A SELECT query that uses COUNT(...) returns the number of rows or values that satisfy the query. The three main forms are COUNT(*) for counting all rows, COUNT(expression) for counting non-NULL expression values, and COUNT(DISTINCT expression) for counting unique non-NULL values. COUNT can be combined with FROM, WHERE, GROUP BY, and HAVING to answer questions such as how many completed orders belong to each customer.

This tutorial explains syntax, NULL behavior, performance considerations, conditional counting with CASE WHEN, joins, subqueries, and database-specific behavior for MySQL 8.x, PostgreSQL 15+, SQL Server 2022, and Oracle 19c. Every example uses the same two-table schema, which can be copied into a compatible database.

Key Takeaways

• COUNT is an aggregate function that summarizes the number of rows or non-NULL values after filtering has taken place.
• COUNT(*), COUNT(column), and COUNT(DISTINCT column) serve different purposes when duplicates and NULL values are involved.
• COUNT(*) includes every row in the input, while COUNT(column) ignores rows where that specific column contains NULL.
• Combine COUNT with GROUP BY to calculate totals for individual groups, and use HAVING when grouped results need to be filtered.
• COUNT(DISTINCT column) eliminates duplicate non-NULL values before counting them, and NULL itself is not included.
• COUNT(CASE WHEN ... THEN 1 END) can calculate several independent conditional totals during a single pass through a table.
• Approximate counting features vary between database engines and should be treated as optional performance tools rather than default replacements for exact counts.

Prerequisites

• Access to a SQL client connected to a database in which read queries can be executed.
• A sample database running MySQL 8.x, PostgreSQL 15+, SQL Server 2022, or Oracle 19c+ if you want to run the examples yourself.
• Basic knowledge of SELECT, INSERT, and CREATE TABLE statements for loading the example data.

Sample Schema Used in This Tutorial

Run the following DDL and DML statements once. NULL values in the status and amount columns make it possible to demonstrate NULL handling. Customers share cities so that GROUP BY examples produce useful results, while customer Hank has no orders so that LEFT JOIN behavior can be demonstrated. Plain string date literals are used so that the script works without modification on MySQL 8.x, PostgreSQL 15+, SQL Server 2022, and Oracle 19c.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(100),
    city        VARCHAR(100),
    status      VARCHAR(20),
    signup_date DATE
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    amount      DECIMAL(10, 2),
    status      VARCHAR(20),
    order_date  DATE
);

INSERT INTO customers (customer_id, name, city, status, signup_date) VALUES
(1, 'Alice', 'Austin', 'active', '2024-01-15'),
(2, 'Bob', 'Austin', NULL, '2024-02-10'),
(3, 'Carol', 'Boston', 'active', '2024-03-05'),
(4, 'Dan', 'Boston', 'pending', '2024-04-20'),
(5, 'Eve', 'Chicago', 'active', '2024-05-12'),
(6, 'Frank', 'Chicago', 'inactive', '2024-06-01'),
(7, 'Grace', 'Denver', 'active', '2024-07-08'),
(8, 'Hank', 'Denver', 'active', '2024-09-01');

INSERT INTO orders (order_id, customer_id, amount, status, order_date) VALUES
(101, 1, 100.00, 'completed', '2024-10-01'),
(102, 1, NULL, 'pending', '2024-10-02'),
(103, 2, 50.00, 'completed', '2024-10-03'),
(104, 2, 75.50, 'cancelled', '2024-10-04'),
(105, 3, 200.00, 'completed', '2024-10-05'),
(106, 3, 120.00, 'pending', '2024-10-06'),
(107, 4, 90.00, 'completed', '2024-10-07'),
(108, 5, 45.00, 'pending', '2024-10-08'),
(109, 5, 60.00, 'completed', '2024-10-09'),
(110, 6, 30.00, 'cancelled', '2024-10-10'),
(111, 7, 85.00, 'completed', '2024-10-11'),
(112, 7, 95.00, 'pending', '2024-10-12'),
(113, 3, 110.00, 'completed', '2024-10-13');

Related SQL concepts include SQL JOINs and the aggregate functions SUM, AVG, and COUNT.

What Is the SQL COUNT Function?

Use COUNT whenever you need to determine how many rows exist, how many values in a column are not NULL, or how many unique values are present. It is one of the most frequently used aggregate functions in SQL and appears in dashboards, data-validation queries, pagination logic, and reporting processes across relational database systems. It operates after WHERE filtering, works with window functions, and can cause incorrect results when the different COUNT variants are mixed without considering their NULL-handling rules.

COUNT Syntax and Parameters

COUNT has three common forms. Select the version that corresponds to the information you want to measure.

-- Count every row, including rows where some columns are NULL
SELECT COUNT(*) FROM table_name;

-- Count rows where the given column is NOT NULL
SELECT COUNT(column_name) FROM table_name;

-- Count unique non-NULL values in a column
SELECT COUNT(DISTINCT column_name) FROM table_name;

Keep the following rules in mind:

• Any expression that evaluates to NULL for a row is ignored by COUNT(expression).
• Standard SQL permits only one argument inside COUNT(DISTINCT ...); a derived table can be used when several columns must be counted as a distinct combination.
• COUNT returns 0 when there are no matching input rows. In contrast, SUM, AVG, MIN, and MAX return NULL.

Use WHERE for filters that must be applied before aggregation and HAVING for conditions that should be evaluated after GROUP BY.

What COUNT Returns and How It Handles NULL Values

In simple terms, COUNT(*) counts rows regardless of NULL values, COUNT(column) ignores rows in which that column is NULL, and COUNT(DISTINCT column) excludes both NULL values and duplicate values.

If one report displays eight customers while another displays seven, first check whether one query uses COUNT(*) and the other uses COUNT(status).

Run this query against the sample schema:

SELECT COUNT(*) AS customer_rows, COUNT(status) AS non_null_status
FROM customers;

Output:

 customer_rows | non_null_status
---------------+-----------------
             8 |               7

customer_rows equals 8 because the customers table contains eight rows. non_null_status equals 7 because Bob has a NULL value in status, causing COUNT(status) to ignore that row.

What About COUNT(1)?

Older Oracle and DB2 codebases commonly contain COUNT(1). Because the literal value 1 is never NULL, it counts every row in the same way as COUNT(*).

Note: COUNT(*) and COUNT(1) generate the same query plan in MySQL 8.x, PostgreSQL 15+, SQL Server 2019+, and Oracle 19c+. Both request row cardinality without requiring a column value to be inspected. The old assumption that COUNT(1) performs better originated from an Oracle 7 optimizer behavior that was corrected long ago. For new SQL, COUNT(*) is generally preferable because it is the standard form for counting rows.

Counting NULL Values

A common related question is how to count rows where a particular column actually contains NULL. COUNT does not provide a direct NULL-counting form, but the following two approaches solve the problem:

-- Pattern 1: subtract non-NULL count from total
SELECT COUNT(*) - COUNT(status) AS null_status_count
FROM customers;

-- Pattern 2: count a CASE expression that emits 1 only for NULL
SELECT COUNT(CASE WHEN status IS NULL THEN 1 END) AS null_status_count
FROM customers;

Output from both queries:

 null_status_count
-------------------
                 1

The CASE approach extends conveniently to multiple columns, while subtraction remains more concise in situations where selective indexes can be used.

COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)

Behavioral Differences with NULL Values

The following query asks three different questions about the same orders table: how many rows exist in total, how many rows contain a non-NULL amount, and how many distinct customers placed orders.

SELECT COUNT(*) AS all_orders,
       COUNT(amount) AS orders_with_amount,
       COUNT(DISTINCT customer_id) AS distinct_buyers
FROM orders;

Output:

 all_orders | orders_with_amount | distinct_buyers
------------+--------------------+-----------------
         13 |                 12 |               7

all_orders is 13 because the table contains thirteen rows. orders_with_amount is 12 because order 102 contains NULL in the amount column and is therefore skipped by COUNT(amount). distinct_buyers is 7 because seven different customers have placed orders, while Hank has no orders.

Performance Considerations and Index Usage

In general, COUNT(*) and COUNT(1) are efficient and equivalent. COUNT(column) behaves similarly while excluding NULL values. COUNT(DISTINCT column) is usually the most expensive option because duplicate values must be removed before counting, and this cost can rise substantially as the dataset grows when no suitable covering index exists.

COUNT(*) and COUNT(1)

Both forms request the number of rows without requiring column payloads to be read. Query planners in MySQL 8.x, PostgreSQL 15+, SQL Server 2019+, and Oracle 19c+ generally choose the same execution plan for both forms and often select the smallest available index that can answer the query. If COUNT(*) on a large table suddenly becomes slower, inspect the plan with EXPLAIN ANALYZE in PostgreSQL or EXPLAIN FORMAT=TREE in MySQL 8.x.

COUNT(column)

An index-only execution path can help when the counted column is indexed. Because NULL values are omitted, the resulting total can be lower than COUNT(*). Performance improvements compared with COUNT(*) are most noticeable on large tables containing very wide rows.

COUNT(DISTINCT column)

Removing duplicates usually requires a sort or hash operation. Without a covering index, scans across hundreds of millions of rows can take minutes.

When COUNT(DISTINCT) becomes expensive, three common approaches can help:

• Covering index: Create an index over the distinct columns so that the planner can process sorted keys instead of hashing complete table data.
• Approximate counts: Features such as APPROX_COUNT_DISTINCT in SQL Server 2019+ or PostgreSQL hll can be appropriate for dashboards where exact values are not required.
• Materialized rollups: Precalculate repeated distinct totals when the same count is needed for frequent page loads or reports.

Note: Running EXPLAIN ANALYZE SELECT COUNT(DISTINCT customer_id) FROM orders; in PostgreSQL can reveal whether the database selected an indexed Aggregate -> Sort plan or a more memory-intensive Aggregate -> HashAggregate approach. Inspect the execution plan before making tuning changes.

Comparison Table: When to Use Each COUNT Variant

SQL SELECT COUNT with a WHERE Clause

WHERE filters rows before aggregation occurs, which means COUNT only processes rows that satisfy the specified condition.

Counting Rows That Match a Single Condition

A single WHERE condition limits the input before COUNT is evaluated. The following query counts orders whose status is 'completed':

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

Output:

 completed_orders
------------------
                7

Seven of the thirteen rows in orders have status = 'completed'. The remaining six rows have either 'pending' or 'cancelled' status values.

Counting Rows with Multiple Conditions Using AND and OR

Conditions can be combined with AND and OR when rows must satisfy more complex criteria. The next query counts completed orders whose amount is greater than 50:

SELECT COUNT(*) AS completed_large_orders
FROM orders
WHERE status = 'completed' AND amount > 50;

Output:

 completed_large_orders
------------------------
                      6

Six of the seven completed orders are above the threshold. Order 103 is not included because its amount is exactly 50, while the condition uses a strict greater-than operator.

Counting Within a Date Window

Reports often need to determine how many events occurred during a recent time period. Use CURRENT_DATE in PostgreSQL and Oracle, CURDATE() in MySQL, or GETDATE() in SQL Server within the filtering condition.

-- PostgreSQL 15+ / Oracle 19c
SELECT COUNT(*) AS orders_last_7_days
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '7 days';

-- MySQL 8.x
SELECT COUNT(*) AS orders_last_7_days
FROM orders
WHERE order_date >= CURDATE() - INTERVAL 7 DAY;

-- SQL Server 2022
SELECT COUNT(*) AS orders_last_7_days
FROM orders
WHERE order_date >= DATEADD(day, -7, CAST(GETDATE() AS DATE));

All three statements return the same value for the sample data when the current date is 2024-10-13:

 orders_last_7_days
--------------------
                  8

Add a b-tree index on order_date when this type of date condition is executed frequently in production.

SQL SELECT COUNT with GROUP BY

Grouping Results and Counting Per Group

GROUP BY produces one result row for each distinct value in the grouping column, while COUNT reports how many input rows belong to each group. The following query counts customers in each city:

SELECT city, COUNT(*) AS customers_in_city
FROM customers
GROUP BY city
ORDER BY city;

Output:

 city    | customers_in_city
---------+-------------------
 Austin  |                 2
 Boston  |                 2
 Chicago |                 2
 Denver  |                 2

Each city returns 2 because the sample data intentionally contains two customers for every city. Counts would normally vary in real datasets, and ordering by the count in descending order is a common way to rank groups according to size.

Filtering Grouped Counts with HAVING

HAVING applies conditions after grouping has taken place, whereas WHERE filters individual input rows before aggregation. Joining customers and orders and then filtering by the resulting count illustrates the distinction.

SELECT c.city, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.city
HAVING COUNT(o.order_id) >= 4
ORDER BY c.city;

Output:

 city   | order_count
--------+-------------
 Austin |           4
 Boston |           4

Chicago and Denver are omitted because their order totals, 3 and 2, do not meet the HAVING threshold.

SQL SELECT COUNT with DISTINCT

DISTINCT removes duplicate values before COUNT calculates the final total.

Counting Unique Values in a Column

COUNT(DISTINCT column) reports the number of unique non-NULL values in a column. It is appropriate when the question is how many different values exist rather than how many rows reference those values. The following query counts distinct cities in the customers table:

SELECT COUNT(DISTINCT city) AS distinct_cities
FROM customers;

Output:

 distinct_cities
-----------------
               4

The eight customers are distributed across four cities: Austin, Boston, Chicago, and Denver. DISTINCT removes repeated city values before COUNT returns 4.

COUNT DISTINCT vs COUNT on a Deduplicated Subquery

To count distinct combinations involving two or more columns, place a DISTINCT projection inside a derived table and count the rows returned by that query. Because the orders table can contain several entries for one customer, the combination of customer_id and status provides a useful example.

SELECT COUNT(*) AS distinct_customer_status_pairs
FROM (SELECT DISTINCT customer_id, status FROM orders) AS pairs;

Output:

 distinct_customer_status_pairs
--------------------------------
                             12

The thirteen order rows become twelve distinct (customer_id, status) combinations because Carol has duplicate 'completed' combinations that are merged.

Why Not COUNT(DISTINCT customer_id, status)?

Standard SQL permits only one expression inside COUNT(DISTINCT ...). PostgreSQL and Oracle reject the multi-column form directly. SQL Server and MySQL allow it with database-specific considerations involving NULL handling that may vary between versions. Using a derived table provides a portable approach with consistent behavior.

Performance Note

Both COUNT(DISTINCT ...) and a derived-table solution incur the cost of deduplication. An index that covers every column used by DISTINCT can reduce sorting work. Without such an index, the optimizer may need a hash operation or an external sort. Examine the execution plan with EXPLAIN ANALYZE in PostgreSQL, EXPLAIN FORMAT=TREE in MySQL 8.x, or SET STATISTICS PROFILE ON in SQL Server.

SQL COUNT with CASE WHEN

Conditional Counting Using CASE WHEN Inside COUNT

COUNT(CASE WHEN ... THEN 1 END) supplies a non-NULL value to COUNT only when a specified condition is true. This makes it possible to calculate several conditional totals with one scan of a table. The following statement returns an order count for each status:

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

Output:

 completed_orders | pending_orders | cancelled_orders
------------------+----------------+------------------
                7 |              4 |                2

The three totals equal 13 when added together, matching COUNT(*) FROM orders. Running three independent queries with separate WHERE conditions would require three table scans, whereas the CASE WHEN version can calculate all three totals in one scan.

Counting Multiple Conditions in a Single Query

Each CASE expression can refer to different columns or combine several conditions with AND and OR. This allows one scan to produce several unrelated conditional totals. The following example counts completed orders that contain an amount and pending orders where the amount is missing:

SELECT
    COUNT(CASE WHEN status = 'completed' AND amount IS NOT NULL THEN 1 END) AS completed_paid,
    COUNT(CASE WHEN status = 'pending' AND amount IS NULL THEN 1 END) AS pending_missing_amount
FROM orders;

Output:

 completed_paid | pending_missing_amount
----------------+------------------------
              7 |                      1

SQL COUNT with JOINs

JOIN operations can multiply rows before COUNT is evaluated. Failing to account for this behavior can produce totals that appear valid during testing but become inaccurate when real one-to-many relationships are present.

The Fan-Out Problem

A common mistake is counting customers after joining them to orders in order to filter according to order status:

SELECT COUNT(*) AS customer_count
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

Output:

 customer_count
----------------
              7

The result 7 represents completed order rows rather than unique customers. Carol contributes two rows because she has two orders whose status is 'completed'.

Use COUNT(DISTINCT) on the customer key when distinct customers are required:

SELECT COUNT(DISTINCT c.customer_id) AS customers_with_completed_orders
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'completed';

Output:

 customers_with_completed_orders
---------------------------------
                               6

Whenever a one-to-many join supplies rows to an aggregate, determine whether the goal is to count rows on the many side with COUNT(*) or COUNT(many_table.id), or distinct identities on the one side with COUNT(DISTINCT one_table.id). Confusing the two produces subtle counting errors.

COUNT with INNER JOIN

INNER JOIN retains only customers with at least one matching order, which means its totals differ from outer-join versions when unmatched customers exist. The following statement groups rows by customer and counts each customer’s orders:

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

Output:

 name  | order_count
-------+-------------
 Alice |           2
 Bob   |           2
 Carol |           3
 Dan   |           1
 Eve   |           2
 Frank |           1
 Grace |           2

Hank does not appear because no matching rows exist for him in orders. The INNER JOIN removes him from the result entirely. A LEFT JOIN retains customers without matching orders and therefore requires a deliberate choice about how those rows should be counted.

COUNT with LEFT JOIN and Handling NULL Counts

LEFT JOIN keeps customers even when they have no matching orders. COUNT(*) includes the padded joined row created when the right side has no match, whereas COUNT(o.order_id) ignores that row because the order ID is NULL.

Warning: After a LEFT JOIN, COUNT(*) counts the joined row even if every column from the right-hand table is NULL. COUNT(o.order_id) counts only actual matched orders. Choosing the wrong form changes totals for entities that have no matching child rows.

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

Output:

 name  | rows_after_join | matched_orders
-------+-----------------+----------------
 Alice |               2 |              2
 Bob   |               2 |              2
 Carol |               3 |              3
 Dan   |               1 |              1
 Eve   |               2 |              2
 Frank |               1 |              1
 Grace |               2 |              2
 Hank  |               1 |              0

Hank demonstrates the difference. rows_after_join is 1 because the LEFT JOIN produces one padded row in which every orders column is NULL. matched_orders is 0 because COUNT(o.order_id) ignores the NULL value. Selecting the incorrect COUNT form can therefore change the apparent result from zero to one.

SQL COUNT in Subqueries and Derived Tables

Using COUNT in a WHERE Clause Subquery

A correlated subquery executes for each row produced by the outer query and can use COUNT to compare each customer with that customer’s own aggregate value. The following example selects customers who have more than two orders:

SELECT name, city
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 2;

Output:

 name  | city
-------+--------
 Carol | Boston

Carol is the only customer that matches because she has three orders: 105, 106, and 113. Every other customer has no more than two. Correlated subqueries are straightforward to write but can become expensive at scale because the inner query is repeated for each outer row. When only the existence of matching rows matters, EXISTS can often be a better option.

Using COUNT as a Derived Table Expression

Derived tables make aggregate values available to an outer WHERE clause in a clear structure.

SELECT city_rollups.city, city_rollups.order_count
FROM (
    SELECT c.city, COUNT(o.order_id) AS order_count
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    GROUP BY c.city
) AS city_rollups
WHERE city_rollups.order_count >= 3
ORDER BY city_rollups.city;

Output:

 city    | order_count
---------+-------------
 Austin  |           4
 Boston  |           4
 Chicago |           3

SQL COUNT Across Database Dialects

Note: Features such as APPROX_COUNT_DISTINCT and PostgreSQL hll exchange some accuracy for increased speed. They should not be used for calculations such as financial ledgers that require exact values.

COUNT in MySQL

With InnoDB, COUNT(*) can be relatively efficient when the optimizer can scan a narrow secondary index rather than the larger clustered primary index.

-- MySQL 8.x
SELECT COUNT(*) AS orders_total FROM orders;

Output:

 orders_total
--------------
           13

Why InnoDB Can Choose a Secondary Index

Clustered index leaf pages contain complete rows, while secondary index leaves contain keys and pointers. As a result, COUNT(*) can prefer the smallest secondary index when processing wide tables. Use EXPLAIN to identify the index selected by the optimizer:

-- MySQL 8.x
EXPLAIN SELECT COUNT(*) FROM orders;

Sample output, with columns that can vary slightly depending on the MySQL release:

 id | select_type | table  | type  | key             | rows | Extra
----+-------------+--------+-------+-----------------+------+-------------
  1 | SIMPLE      | orders | index | idx_customer_id |   13 | Using index

The key field identifies the index selected by the optimizer. A value other than PRIMARY, such as idx_customer_id, indicates that a secondary index was selected. Using index in the Extra field confirms that the query can be answered directly from the index without loading the complete row data.

Legacy MyISAM Note

If an older MySQL table returns COUNT(*) almost instantly even with an extremely large number of rows, verify the table’s storage engine before assuming that the optimizer has found an unusually efficient execution strategy:

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name = 'orders';

Output:

 table_name | engine
------------+--------
 orders     | InnoDB

MyISAM stored an exact row total in table metadata and could therefore answer an unfiltered COUNT(*) without scanning rows. InnoDB does not maintain the same kind of universally valid count because MVCC means that the visible number of rows depends on the transaction snapshot. This difference often becomes noticeable when older MyISAM tables are migrated to InnoDB, and the engine field in information_schema.tables provides a quick way to verify the active storage engine.

COUNT in PostgreSQL, Including Window Function Usage

PostgreSQL supports COUNT as a window function through syntax such as COUNT(*) OVER (PARTITION BY ...). Unlike GROUP BY, which reduces each group to one output row, a window function preserves every detail row and adds the group total alongside it. This is useful when a report needs individual row data and per-group totals in the same result set:

-- PostgreSQL 15+
SELECT customer_id,
       name,
       city,
       COUNT(*) OVER (PARTITION BY city) AS customers_in_city
FROM customers
ORDER BY city, customer_id;

Output:

 customer_id | name  | city    | customers_in_city
-------------+-------+---------+------------------
           1 | Alice | Austin  |                 2
           2 | Bob   | Austin  |                 2
           3 | Carol | Boston  |                 2
           4 | Dan   | Boston  |                 2
           5 | Eve   | Chicago |                 2
           6 | Frank | Chicago |                 2
           7 | Grace | Denver  |                 2
           8 | Hank  | Denver  |                 2

Each row retains its individual values and receives an additional customers_in_city column containing the total for its partition. The value is 2 for every row in the sample data because every city contains two customers. With real datasets, the value would depend on the corresponding partition.

For workloads involving large distinct counts, PostgreSQL’s hll extension can exchange exact results for constant-memory approximate counting. Install it once for the database:

-- PostgreSQL 15+
CREATE EXTENSION IF NOT EXISTS hll;

The following query estimates the number of distinct customers with HyperLogLog. The nested functions hash each customer_id, combine the hashes into an hll sketch, and then return the estimated cardinality:

-- PostgreSQL 15+
SELECT hll_cardinality(hll_add_agg(hll_hash_integer(customer_id))) AS approx_distinct_buyers
FROM orders;

Output:

 approx_distinct_buyers
------------------------
                      7

The estimate matches the exact result from COUNT(DISTINCT customer_id) because HyperLogLog uses linear counting for small cardinalities. On larger datasets, an error of roughly two percent can be expected while memory usage remains in the low kilobyte range. This type of approximation can be suitable for dashboards and telemetry, while billing and reconciliation should continue to rely on exact COUNT(DISTINCT ...) results.

COUNT in Oracle

Oracle supports the basic COUNT(*) OVER (PARTITION BY ...) window-function pattern in a similar way to PostgreSQL. Oracle-specific conventions include the DUAL table, uppercase unquoted identifiers, and extensive analytic-frame functionality.

DUAL is Oracle’s built-in single-row table. It is commonly used to evaluate an expression without querying application data, making it useful for basic checks in stored procedures and migration scripts:

-- Oracle 19c
SELECT COUNT(*) AS one
FROM dual;

Output:

 ONE
-----
   1

COUNT(*) against DUAL returns 1 because DUAL contains one row. Similar statements are used in existing applications to confirm that a connection is functioning and that procedural code can compile.

Oracle also supports COUNT as an analytic function with explicit window-frame clauses. This makes it possible to generate running totals one row at a time. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW instructs the database to count from the first ordered row through the current row:

-- Oracle 19c
SELECT order_id,
       order_date,
       COUNT(*) OVER (
           ORDER BY order_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_order_count
FROM orders
ORDER BY order_date;

Output, shortened in the original example:

 ORDER_ID | ORDER_DATE  | RUNNING_ORDER_COUNT
----------+-------------+---------------------
      101 | 2024-10-01  |                   1
      102 | 2024-10-02  |                   2
      103 | 2024-10-03  |                   3
      ...
      113 | 2024-10-13  |                  13

Unquoted Oracle identifiers are normally displayed in uppercase, as shown by ORDER_ID. For approximate cardinality calculations, Oracle 12c version 12.1.0.2 and later provide APPROX_COUNT_DISTINCT(column), including Oracle 19c and 23c. It can be used when an exact COUNT(DISTINCT ...) query takes too long, while frequently reused approximate totals can also be stored in materialized rollups.

COUNT in SQL Server with Transact-SQL

SQL Server 2019 introduced APPROX_COUNT_DISTINCT as a built-in HyperLogLog-based alternative to exact COUNT(DISTINCT ...). It is useful when dashboards need distinct totals from tables containing hundreds of millions of rows and can accept approximately two percent error in exchange for predictable memory requirements and faster execution.

-- SQL Server 2022
SELECT APPROX_COUNT_DISTINCT(customer_id) AS approx_buyers
FROM orders;

Output:

 approx_buyers
---------------
             7

The result is exactly 7 in this example because SQL Server, like PostgreSQL hll, uses linear counting for small cardinalities. On a table containing a billion rows, the approximate result would be expected to remain within roughly two percent of the exact value while potentially completing much faster. This makes the function suitable for monitoring and capacity-planning queries, while exact COUNT(DISTINCT ...) remains appropriate for financial reporting.

Documentation: COUNT and APPROX_COUNT_DISTINCT.

Frequently Asked Questions

What Is the Difference Between COUNT(*) and COUNT(column_name) in SQL?

COUNT(*) includes every row in the result set, even if one or more columns contain NULL. COUNT(column_name) includes only rows where the specified column is not NULL. Use COUNT(*) when the goal is to calculate the total number of rows.

Does SQL COUNT Include NULL Values?

COUNT(*) includes rows even when they contain NULL values. COUNT(column_name) excludes NULL values found in that particular column. COUNT(DISTINCT column_name) removes both NULL values and duplicates before calculating the total.

How Do I Count Rows That Meet a Specific Condition in SQL?

Apply a WHERE condition before COUNT, for example SELECT COUNT(*) FROM orders WHERE status = 'completed';. When several different conditions need to be counted in the same query, place individual CASE WHEN expressions inside COUNT.

How Does COUNT Work with GROUP BY?

GROUP BY defines groups of rows, and COUNT returns one total for each group. Columns in the SELECT list that are not aggregated must also appear in GROUP BY or be placed inside an aggregate function.

What Is the Performance Difference Between COUNT(*) and COUNT(DISTINCT column)?

COUNT(*) does not perform duplicate elimination. COUNT(DISTINCT column) normally requires sorting or hashing unless an appropriate covering index reduces that work.

Can I Use COUNT with a JOIN in SQL?

Yes. A one-to-many join can create multiple joined rows before aggregation. With LEFT JOIN, use COUNT(*) only when an unmatched row should still be represented as one padded result row. Otherwise, count a non-NULL key from the joined table.

How Do I Use COUNT with DISTINCT in SQL?

Use SELECT COUNT(DISTINCT column_name) FROM table_name; to count unique non-NULL values. The basic syntax is consistent across MySQL 8.x, PostgreSQL 15+, SQL Server 2022, and Oracle 19c.

Does COUNT Work the Same Way in MySQL, PostgreSQL, Oracle, and SQL Server?

The fundamental COUNT forms behave consistently for standard SQL-style queries. Differences appear in areas such as window-function syntax, support for APPROX_COUNT_DISTINCT, InnoDB execution strategies for an unfiltered COUNT(*), and PostgreSQL’s optional hll extension.

When Should I Use EXISTS Instead of COUNT?

Use EXISTS when the required result is simply whether at least one matching row exists. A condition such as WHERE (SELECT COUNT(*) ...) > 0 counts every matching row before returning the result, while EXISTS can stop as soon as the first matching row is found.

-- Slow on large tables: counts every matching row before returning
SELECT name FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) > 0;

-- Fast: stops at the first match
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Both statements return seven customers for this sample dataset, which means everyone except Hank. The performance difference becomes larger when the joined or related table contains many rows.

Why Did My COUNT Return Zero Instead of NULL on an Empty Table?

COUNT returns 0 when its input contains no rows. By comparison, SUM, AVG, MIN, and MAX return NULL. For this reason, wrapping COUNT in COALESCE simply to replace NULL with zero is unnecessary.

Conclusion

This tutorial explained the three forms of the COUNT aggregate function and the rules for NULL values and duplicates that distinguish them. It covered filtering before aggregation with WHERE, filtering grouped results with HAVING, calculating per-group totals with GROUP BY, conditional counting with CASE WHEN, one-to-many behavior with INNER JOIN and LEFT JOIN, subqueries and derived tables, and database-specific behavior in MySQL 8.x, PostgreSQL 15+, SQL Server 2022, and Oracle 19c. It also examined approximate counting options such as APPROX_COUNT_DISTINCT and PostgreSQL’s hll extension.

You can use these patterns to select the appropriate COUNT form for different requirements, calculate NULL totals, prevent duplicate overcounting after one-to-many joins, distinguish row totals from distinct-entity totals, inspect execution plans before optimizing slow COUNT(DISTINCT ...) queries, and move between portable ANSI SQL and database-specific features.

For further study, review GROUP BY, JOIN, and DISTINCT concepts and practice fundamental SQL query patterns. When testing these techniques with real workloads, a managed database environment can help keep development and test traffic separate from production systems.