A SELECT query with COUNT(...) returns how many rows or values satisfy the query. The three forms are COUNT(*) for total rows, COUNT(expression) for non-NULL values of an expression, and COUNT(DISTINCT expression) for unique non-NULL values. COUNT pairs with FROM, WHERE, GROUP BY, and HAVING to answer questions like “how many completed orders does each customer have.”

This tutorial covers syntax, NULL handling, performance trade-offs, conditional counting with CASE WHEN, joins, subqueries, and dialect-specific behavior on MySQL 8.x, PostgreSQL 15+, SQL Server 2022, and Oracle 19c. Every example runs against a shared two-table schema you can copy into your own database.

• COUNT is an aggregate that summarizes row counts or non-NULL values after filters are applied.
• COUNT(*), COUNT(column), and COUNT(DISTINCT column) answer different questions about duplicates and NULL handling.
• COUNT(*) counts every row in the grouped input; COUNT(column) skips rows where that column is NULL.
• Pair COUNT with GROUP BY to return per-group totals, and add HAVING to filter the grouped results.
• COUNT(DISTINCT column) removes duplicate non-NULL values before counting; NULL is never counted inside DISTINCT.
• COUNT(CASE WHEN ... THEN 1 END) tallies rows that satisfy independent conditions in a single table pass.
• Approximate helpers differ by engine; treat them as opt-in accelerators, not defaults.

• Access to a SQL client connected to a database where you can run read queries.
• A sample database on MySQL 8.x, PostgreSQL 15+, SQL Server 2022, or Oracle 19c+ for optional hands-on runs.
• Basic SELECT, INSERT, and CREATE TABLE skills to load the seed script.

Run this DDL and DML once. NULL status and amount values demonstrate NULL handling, shared cities support GROUP BY examples, and customer Hank has no orders to demonstrate LEFT JOIN behavior. The seed script uses plain string date literals so it runs unchanged 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');

See also SQL JOINs and SUM, AVG, and COUNT.

Use COUNT when you need to know how many rows exist, how many non-NULL values a column has, or how many unique values appear. It is the most common aggregate function in SQL and shows up in dashboards, validation queries, pagination logic, and reporting jobs across every relational database. It runs after WHERE filters, pairs cleanly with window frames, and still trips teams when someone mixes the three forms without checking NULL rules first.

COUNT Syntax and Parameters

COUNT has three forms. Pick the form that matches the question you are asking.

-- 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;

Rules that save debugging time later:

• Any expression that resolves to NULL for a row is skipped by COUNT(expression).
• Standard SQL allows only one argument inside COUNT(DISTINCT ...); use a derived table for multi-column distinct counts (shown later).
• COUNT returns 0 on empty input, unlike SUM, AVG, MIN, and MAX, which return NULL.

Filters belong in WHERE when applied before aggregation, or in HAVING when applied after GROUP BY.

What COUNT Returns and How It Handles NULL Values

The short answer: COUNT(*) counts rows regardless of NULL, COUNT(column) skips rows where that column is NULL, and COUNT(DISTINCT column) skips both NULLs and duplicates.

If one screen shows 8 customers and another shows 7, compare whether each query used COUNT(*) versus COUNT(status) before you chase ghosts in the warehouse.

Run the 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 is 8 because there are 8 rows in customers. non_null_status is 7 because Bob’s status is NULL, so COUNT(status) skips that row.

What About COUNT(1)?

Legacy Oracle and DB2 codebases often use COUNT(1); the literal is never NULL, so it counts every row just like COUNT(*).

Note: COUNT(*) and COUNT(1) produce the same query plan in MySQL 8.x, PostgreSQL 15+, SQL Server 2019+, and Oracle 19c+. Both express row cardinality without inspecting column payloads. The historical belief that COUNT(1) is faster traces back to an Oracle 7 optimizer quirk fixed decades ago. Use COUNT(*) in new code; it is the SQL standard form.

Counting NULLs Themselves

A common follow-up question: how do you count rows where a column is NULL? COUNT cannot do this directly, but two patterns work:

-- 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 (both queries):

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

CASE scales to multiple columns; subtraction stays shorter with selective indexes.

Behavioral Differences with NULL Values

The query below asks all three questions of the same orders table at once: how many rows total, how many have a non-NULL amount, and how many unique buyers 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 there are 13 rows in orders. orders_with_amount is 12 because order 102 has a NULL amount, so COUNT(amount) skips it. distinct_buyers is 7 because seven different customers placed orders (Hank has none).

Performance Considerations and Index Usage

The short answer: COUNT(*) and COUNT(1) are fast and equivalent. COUNT(column) is similar but skips NULLs. COUNT(DISTINCT column) is the expensive one because the planner has to deduplicate before counting, and that cost grows superlinearly with row count when no covering index is available.

COUNT(*) and COUNT(1)

Both ask for row cardinality without reading column payloads. Planners on MySQL 8.x, PostgreSQL 15+, SQL Server 2019+, and Oracle 19c+ pick the same plans for the two and usually prefer the smallest index that answers the question. Use EXPLAIN ANALYZE (PostgreSQL) or EXPLAIN FORMAT=TREE (MySQL 8.x) when a large-table COUNT(*) suddenly regresses.

COUNT(column)

Index-only paths help when the column is indexed, and NULL rules mean the number can sit below COUNT(*). The win over COUNT(*) shows up mainly on very wide rows and large tables.

COUNT(DISTINCT column)

Deduplication forces a sort or hash. Without a covering index, expect minutes on hundred-million-row scans.

Three strategies when COUNT(DISTINCT) hurts:

• Covering index on the distinct columns so the planner can stream sorted keys instead of hashing the heap.
• Approximate counts via APPROX_COUNT_DISTINCT (SQL Server 2019+) or PostgreSQL hll for dashboards, not billing.
• Materialized rollups when the same distinct count powers every page load.

Note: EXPLAIN ANALYZE SELECT COUNT(DISTINCT customer_id) FROM orders; shows whether PostgreSQL chose Aggregate -> Sort (indexed) or Aggregate -> HashAggregate (heap heavy). Read the plan before tuning.

Comparison Table: When to Use Each Variant

WHERE filters rows before aggregation, so COUNT only sees rows that match the predicate.

Counting Rows That Match a Single Condition

A single WHERE predicate filters the input before COUNT evaluates it. The query below counts how many orders have a status of '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 other six are split between 'pending' and 'cancelled'.

Counting Rows with Multiple Conditions Using AND and OR

Combine predicates with AND and OR to count rows that satisfy compound conditions. The query below counts completed orders with amount 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 clear the threshold. Order 103 is excluded because its amount is exactly 50, and the predicate uses strict greater-than.

Counting Within a Date Window

Dashboards often ask how many events landed in the last week. Use CURRENT_DATE (PostgreSQL, Oracle), CURDATE() (MySQL), or GETDATE() (SQL Server) inside the predicate:

-- 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 queries return the same result against the sample data when the current date is 2024-10-13:

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

Add a b-tree on order_date if this predicate runs hot in production.

Grouping Results and Counting Per Group

GROUP BY emits one row per distinct value in the grouping column, with COUNT reporting the row count for each group. The query below counts customers per 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 shows 2 because the seed data deliberately places two customers per city. On a real dataset the counts would vary, and ORDER BY count DESC is the common pattern for ranking groups by size.

Filtering Grouped Counts with HAVING

HAVING filters after grouping, unlike WHERE, which filters raw rows before aggregation. Joining customers to orders and filtering on the join count shows the difference clearly.

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 excluded because their order counts (3 and 2) fall below the HAVING threshold.

DISTINCT removes duplicate values before COUNT evaluates the set.

Counting Unique Values in a Column

COUNT(DISTINCT column) counts how many unique non-NULL values appear in a column. Use it when the question is “how many different X are there,” not “how many rows reference X.” The query below counts how many distinct cities appear in customers:

SELECT COUNT(DISTINCT city) AS distinct_cities
FROM customers;

Output:

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

Eight customers share four cities (Austin, Boston, Chicago, Denver), so DISTINCT collapses the duplicates and COUNT returns 4.

COUNT DISTINCT vs COUNT on a Deduplicated Subquery

To count distinct combinations of two or more columns, wrap a DISTINCT projection in a derived table and count the result. The orders table has multiple rows per customer, so (customer_id, status) pairs actually deduplicate, which makes them good for showing the pattern.

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

Output:

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

Thirteen rows collapse to twelve distinct (customer_id, status) pairs (Carol’s duplicate 'completed' rows merge).

Why Not COUNT(DISTINCT customer_id, status)?

Standard SQL only allows one expression inside COUNT(DISTINCT ...). PostgreSQL and Oracle reject the multi-column form outright. SQL Server and MySQL allow it but with caveats around NULL handling that change between versions. The derived-table form is portable and behaves consistently everywhere.

Performance Note

COUNT(DISTINCT ...) and the derived-table form both pay deduplication cost. Indexes that cover every column inside DISTINCT keep sorts cheap; otherwise expect hash or external sort plans. Confirm with EXPLAIN ANALYZE (PostgreSQL), EXPLAIN FORMAT=TREE (MySQL 8.x), or SET STATISTICS PROFILE ON (SQL Server).

Conditional Counting Using CASE WHEN Inside COUNT

COUNT(CASE WHEN ... THEN 1 END) feeds COUNT a non-NULL marker only when the predicate passes, which lets one query report several conditional totals in a single table scan. The query below produces a status breakdown across all orders:

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 counts add up to 13, which matches COUNT(*) FROM orders. Running this as three separate WHERE-filtered queries would scan the table three times; the CASE WHEN form scans once.

Counting Multiple Conditions in a Single Query

Each CASE arm can reference a different column or combine predicates with AND and OR, so a single table scan can produce several conditional totals at once. The pattern below counts completed orders that have an amount alongside pending orders that are missing one:

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

Joins multiply rows before COUNT runs. If you forget that, every dashboard looks fine in QA and drifts in production.

The Fan-Out Problem

Counting customers while joining to orders to filter on status is the textbook mistake:

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

That 7 is the number of completed order rows, not distinct customers. Carol alone contributes two of those rows because she has two 'completed' orders.

The fix is COUNT(DISTINCT) on the dimension key:

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

When a one-to-many join feeds an aggregate, decide whether you care about rows on the many side (COUNT(*), COUNT(many_table.id)) or identities on the one side (COUNT(DISTINCT one_table.id)). Mixing the two ships quiet bugs.

COUNT with INNER JOIN

INNER JOIN keeps only customers who have at least one matching order, so its row totals differ from outer-join variants on the same data. The query below groups by customer and counts each one’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 in the output because he has no rows in orders. INNER JOIN drops him entirely. The next section shows LEFT JOIN, which keeps Hank and forces a decision about how to count him.

COUNT with LEFT JOIN and Handling NULL Counts

LEFT JOIN keeps customers without orders. COUNT(*) counts the padded row where the right side is NULL; COUNT(o.order_id) ignores NULL order ids.

Warning: After a LEFT JOIN, COUNT(*) counts the joined row even when all right-side columns are NULL; COUNT(o.order_id) counts only matched orders. Mixing the two forms changes totals for customers without orders.

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

Look at Hank’s row. rows_after_join is 1 because the LEFT JOIN produced a single padded row for him with all orders columns set to NULL. matched_orders is 0 because COUNT(o.order_id) skips that NULL. Picking the wrong form silently shifts Hank’s total between zero and one, which is how dashboards drift from reality.

Using COUNT in a WHERE Clause Subquery

A correlated subquery runs once per outer row and uses COUNT to compare each customer to their own aggregate. The query below returns 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 match because she has three orders (105, 106, 113). Every other customer has two or fewer. Correlated subqueries are easy to write but expensive at scale because the inner query repeats per outer row; the FAQ at the end of this tutorial covers when to switch to EXISTS instead.

Using COUNT as a Derived Table Expression

Derived tables expose aggregates to outer WHERE clauses cleanly.

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

Note: APPROX_COUNT_DISTINCT and PostgreSQL hll trade accuracy for speed; keep them out of ledgers that require exact balances.

COUNT in MySQL

COUNT(*) on InnoDB is cheap when the planner can walk a narrow secondary index instead of the clustered primary tree.

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

Output:

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

Why InnoDB Picks a Secondary Index

Clustered leaves hold full rows; secondary leaves hold keys plus pointers, so COUNT(*) often prefers the smallest secondary index on wide tables. Use EXPLAIN to confirm which index the planner picked:

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

Sample output (columns vary slightly by MySQL release):

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

The key column shows the index the planner chose. A non-PRIMARY entry like idx_customer_id means the secondary-index shortcut fired. Using index in the Extra column confirms the engine answered the query directly from the index without touching row data.

Legacy MyISAM Note

If you are maintaining a legacy MySQL schema and COUNT(*) returned instantly on a billion-row table, check the storage engine before assuming the optimizer is doing something clever:

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

Output:

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

MyISAM cached the exact row count in the table header and returned COUNT(*) without scanning anything. InnoDB does not, because MVCC means the “true” row count depends on the calling transaction’s snapshot. Migrations from MyISAM to InnoDB are where teams first notice their dashboard totals slowing down overnight, and the engine column in information_schema.tables is the fastest way to confirm the cause.

COUNT in PostgreSQL (Including Window Function Usage)

PostgreSQL supports COUNT as a window function via COUNT(*) OVER (PARTITION BY ...). Unlike GROUP BY, which collapses each partition into a single row, the window form keeps every detail row and adds the partition count alongside it. This is what you want when a report needs both per-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

Every row keeps its full detail and gains a customers_in_city column showing the partition total. The value is 2 for every row because each city has two customers in the seed data; on real data the column would vary by partition.

For heavy distinct workloads on PostgreSQL, the hll extension trades exact answers for constant memory. Install it once per database:

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

The query below approximates the distinct buyer count using HyperLogLog. The three nested calls hash each customer_id, aggregate the hashes into an hll sketch, then read the cardinality estimate from the sketch:

-- 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 result matches the exact COUNT(DISTINCT customer_id) because HyperLogLog falls back to linear counting at small cardinalities. On larger datasets, expect roughly 2% error at constant low-kilobyte memory. Use hll for dashboards and telemetry; keep billing and reconciliation on exact COUNT(DISTINCT ...).

COUNT in Oracle

Oracle matches PostgreSQL on basic COUNT(*) OVER (PARTITION BY ...), so focus on habits you only see on Oracle: DUAL, uppercase identifiers unless quoted, and richer analytic frames.

DUAL is Oracle’s built-in single-row table. It is the standard way to evaluate an expression without touching real data, which makes it useful for smoke-testing logic in stored procedures and migration scripts:

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

Output:

 ONE
-----
   1

COUNT(*) against DUAL always returns 1 because DUAL always has exactly one row. The query shows up in real codebases as a way to verify that a connection works and that a procedure compiles.

Oracle also exposes COUNT as an analytic function with explicit frame clauses, which lets you build running totals row by row. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tells the engine to count from the first row of the partition up through the current row, ordered by order_date:

-- 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 (truncated):

 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 identifiers surface as uppercase (ORDER_ID). For approximate cardinality, Oracle 12c (12.1.0.2) and later expose APPROX_COUNT_DISTINCT(column) directly, including 19c and 23c. Use it when exact COUNT(DISTINCT ...) runs too long, and materialize rollups when the same approximate count powers repeated dashboard queries.

COUNT in SQL Server (Transact-SQL)

SQL Server 2019 introduced APPROX_COUNT_DISTINCT as a built-in HyperLogLog-based alternative to COUNT(DISTINCT ...). It is the right tool when a dashboard needs distinct counts on tables in the hundreds of millions of rows and can tolerate roughly 2% error in exchange for constant memory and predictable response time:

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

Output:

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

The result is exactly 7 here because SQL Server, like PostgreSQL hll, switches to linear counting for small cardinalities. On a billion-row table the result would be within roughly 2% of the true distinct count and would return in seconds rather than minutes. Reach for this in monitoring and capacity-planning queries; keep exact COUNT(DISTINCT ...) for anything that ends up on a financial report.

Docs: COUNT, APPROX_COUNT_DISTINCT.

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

COUNT(*) counts every row in the result set, including rows with NULL values in any column. COUNT(column_name) counts only rows where that column is not NULL. Use COUNT(*) for whole-row totals.

Does SQL COUNT Include NULL Values?

COUNT(*) counts rows that contain NULL somewhere. COUNT(column_name) skips NULL in that column. COUNT(DISTINCT column_name) drops NULL and duplicates before counting.

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

Use WHERE before COUNT, for example SELECT COUNT(*) FROM orders WHERE status = 'completed';. To count rows under several conditions in one query, nest CASE WHEN inside COUNT.

How Does COUNT Work with GROUP BY?

GROUP BY defines partitions; COUNT returns one total per group. Non-aggregated SELECT columns must repeat in GROUP BY or sit inside aggregates.

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

COUNT(*) skips deduplication. COUNT(DISTINCT column) pays sort or hash costs unless a covering index helps.

Can I Use COUNT with a JOIN in SQL?

Yes. One-to-many joins duplicate rows before grouping. With LEFT JOIN, use COUNT(*) only when unmatched dimension rows should register as one padded row; otherwise count a non-NULL fact key.

How Do I Use COUNT with DISTINCT in SQL?

Run SELECT COUNT(DISTINCT column_name) FROM table_name; for unique non-NULL values. Syntax matches 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?

Core COUNT forms match for ANSI-shaped queries. Engines diverge on window syntax, APPROX_COUNT_DISTINCT, InnoDB plans for bare COUNT(*), and PostgreSQL hll.

When Should I Use EXISTS Instead of COUNT?

Prefer EXISTS when you only need a yes or no. WHERE (SELECT COUNT(*) ...) > 0 always walks every match; EXISTS stops at the first hit.

-- 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 return seven customers here (everyone except Hank). The gap widens on wide fact tables.

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

COUNT returns 0 on empty input; SUM, AVG, MIN, and MAX return NULL. Drop redundant COALESCE wrappers around COUNT.

This tutorial covered the three forms of the COUNT aggregate function and the NULL and duplicate rules that distinguish them. It walked through filtering before aggregation with WHERE, filtering grouped results with HAVING, per-group totals with GROUP BY, conditional counting with CASE WHEN, fan-out behavior across INNER JOIN and LEFT JOIN, subquery and derived-table patterns, and dialect-specific behavior on MySQL 8.x, PostgreSQL 15+, SQL Server 2022, and Oracle 19c, including approximate-count helpers like APPROX_COUNT_DISTINCT and the PostgreSQL hll extension.

You can now choose the right COUNT form for any question, count NULL values directly, avoid duplicate overcounting after one-to-many joins, distinguish row totals from distinct-entity totals at a join boundary, read planner output before tuning slow COUNT(DISTINCT ...) queries, and move between portable ANSI SQL and engine-specific helpers without surprises.

To go deeper, read the GROUP BY, JOIN, and DISTINCT tutorials, and rehearse the fundamentals in An Introduction to Queries in MySQL. When you are ready to run these patterns against real workloads, a DigitalOcean Managed Database keeps practice and test traffic isolated from production.