SQL COUNT

What is SQL COUNT and how do you use it?

COUNT returns the number of rows that match a given condition or expression.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL COUNT:

SQL COUNT Full Explanation

COUNT is an ANSI-standard aggregate function that scans the rows produced by a query block and returns a single integer. Its behavior depends on the argument supplied: COUNT(*) tallies every row, including those with NULL values; COUNT(expression) skips rows where the evaluated expression is NULL; COUNT(DISTINCT expression) removes duplicate, non-NULL values before counting. If no qualifying rows exist, COUNT returns 0, never NULL. Because it is computed after WHERE filtering but before ORDER BY, the result reflects the exact set of rows fed into the aggregation layer. COUNT is deterministic and can be combined with GROUP BY, HAVING, window functions (COUNT() OVER …), and other aggregates. Performance is typically O(n) on the filtered result set, but many databases optimize COUNT(*) with metadata or index-only scans when no filters are involved. Caveats: COUNT(column) ignores NULLs, which sometimes surprises users expecting a full row count; COUNT(DISTINCT) can be resource-intensive on large, high-cardinality datasets; and COUNT(*) on views with joins may multiply rows unless DISTINCT or GROUP BY is applied.

SQL COUNT Syntax

-- Count every row
SELECT COUNT(*)
FROM table_name;

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

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

SQL COUNT Parameters

1. * (asterisk) - special token - counts all rows including NULLs
2. expression (any numeric, string, or Boolean expression) - rows where expression is NULL are skipped
3. DISTINCT (optional keyword) - removes duplicate non-NULL values before counting

Example Queries Using SQL COUNT

-- Basic row count
SELECT COUNT(*) AS total_orders
FROM orders;

-- Count shipped orders only
SELECT COUNT(*) AS shipped_orders
FROM orders
WHERE status = 'shipped';

-- Count how many orders have a non-NULL coupon code
SELECT COUNT(coupon_code) AS coupon_used
FROM orders;

-- Count distinct customers who placed an order this year
SELECT COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE order_date >= '2024-01-01';

-- Row count per status
SELECT status, COUNT(*) AS status_count
FROM orders
GROUP BY status;

-- Windowed running count
SELECT order_id,
       COUNT(*) OVER (PARTITION BY customer_id ORDER BY order_date) AS order_sequence
FROM orders;

Expected Output Using SQL COUNT

Each query returns a single integer (or one per group) representing the number of qualifying rows
For GROUP BY, one count per group is produced; for window functions, the count is repeated across rows in the partition

Use Cases with SQL COUNT

Determine table size quickly with COUNT(*)
Measure data completeness by counting non-NULL values in a column
Calculate unique user counts with COUNT(DISTINCT user_id)
Produce grouped metrics such as orders per day
Build windowed running totals or rankings
Validate data migrations by comparing row counts between tables

Common Mistakes with SQL COUNT

Expecting COUNT(column) to include NULL rows - it does not
Forgetting DISTINCT when counting unique values
Using COUNT(*) in a SELECT with joins, causing row multiplication
Assuming COUNT returns NULL when no rows match; it actually returns 0
Placing COUNT in WHERE instead of HAVING when filtering aggregated results

First Introduced In

SQL-92 standard

Frequently Asked Questions

What does COUNT(*) count?

COUNT(*) tallies every row that survives the WHERE clause, even if every column value in a row is NULL.

Why does COUNT(column) return a smaller number than COUNT(*)?

COUNT(column) skips rows where column evaluates to NULL, so tables with missing data often show a difference between the two counts.

How do I get the number of unique users?

Run COUNT(DISTINCT user_id) on the relevant table or subquery. DISTINCT removes duplicate non-NULL user_id values before the count.

Can I filter on COUNT results?

Yes. Use HAVING to filter aggregated results: SELECT department_id, COUNT(*) AS cnt FROM employees GROUP BY department_id HAVING COUNT(*) > 10;