COUNT of COUNT in SQL: Counting the Counts

Short answer

• "Count of count" in SQL means counting the results of an earlier count. First group the rows and count within each group, then count those group counts in an outer query.
• You cannot write COUNT(COUNT(*)) in one SELECT list in PostgreSQL, MySQL, or SQLite. An aggregate function cannot take another aggregate function as its direct argument at the same query level. Put the first count in a subquery or a common table expression, then count its rows in the outer query.
• COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts distinct non-NULL values in that column. The difference matters most when a LEFT JOIN creates rows with NULL values.
• To count grouped counts, group first, name the count, then group or filter on that named count in the outer query.
• If you only need the number of groups, COUNT(*) in the outer query over the grouped result gives that number. If you need the distribution, GROUP BY the inner count in the outer query instead.

A reader who types “count of count sql” is usually stuck on a sentence, not a single function. It sounds simple: count the counts. Count how many customers placed three orders, how many authors wrote five articles, or how many days had more than ten signups. The hard part is that SQL does not let you write that idea in one step. You first have to make the counts, and only then can you count them.

This guide explains that two step idea from the ground up. It starts with what COUNT itself counts, because most count of count mistakes are really COUNT mistakes in disguise. Every worked example uses one small dataset, so you can check each result by hand.

The examples are written for PostgreSQL, MySQL, and SQLite unless a note says otherwise. Where the dialects differ, the difference is labeled.

SHORT-ANSWER BOX

What the Phrase Can Mean

“Count of count” is not a function name. It is a description of a two level question, and the same words can describe three different questions. Naming the question before you write the query prevents most errors.

The first reading is the number of groups. How many customers placed at least one order? How many days had any sales? In each case the inner count creates one row per group, and the outer count counts those rows.

The second reading is the distribution of counts. How many customers placed zero orders, how many placed one order, and how many placed two or more? Here the outer query does not return one number. It returns one row per distinct inner count, with a count beside each one. This is the reading that most tutorials mean when they say “count of count,” because it answers a question that a single COUNT cannot answer.

The third reading is a count after a filter on a count. How many customers placed at least three orders? Here the outer query filters the grouped result, then counts what remains. The filter belongs in the outer query, not in the inner query, because the inner query has not yet produced the count to filter on.

A fourth use looks similar but is not a count of a count at all. COUNT(DISTINCT customer_id) counts distinct values in one pass. It cannot tell you how many customers placed exactly two orders. Only a grouped count followed by an outer count can do that.

Before you write code, finish this sentence: “I want to count how many [groups] have [what count].” If you cannot fill both blanks, the question is not ready for SQL yet.

COUNT Itself, in Four Forms

PostgreSQL documentation defines the core rule in one line each for the two basic forms: COUNT() is the number of input rows, and COUNT(expression) is the number of input rows for which the value of the expression is not NULL. SQLite documentation states the same rule: COUNT(X) returns the number of times X is not NULL in a group, and COUNT() returns the total number of rows in the group. MySQL follows the same core rule: COUNT(*) counts rows whether or not they contain NULL values, while COUNT(column) counts non-NULL values in that column. Those three statements agree.

The four forms are:

SELECT COUNT(*) FROM orders;
SELECT COUNT(customer_id) FROM orders;
SELECT COUNT(DISTINCT customer_id) FROM orders;
SELECT COUNT(*) FROM orders WHERE total >= 30;

COUNT(*) counts every row that reaches the aggregate, including rows where every column except the row itself is NULL. It is the right choice when the question is “how many rows.”

COUNT(column) counts only rows where that column is not NULL. It is the right choice when the question is “how many rows have a value here.” The gap between COUNT(*) and COUNT(column) is exactly the number of rows where the column is NULL, and that gap is often useful as a data quality check.

COUNT(DISTINCT column) counts distinct non-NULL values. NULL is not counted as a value, and repeated values collapse to one. If five orders belong to three customers, COUNT(DISTINCT customer_id) returns 3, while COUNT(*) returns 5 and COUNT(customer_id) returns 5 if no customer_id is NULL.

A filtered count uses WHERE before aggregation. WHERE removes rows first, and COUNT then counts what remains. That differs from filtering groups after aggregation, covered by the companion HAVING article. Do not put a condition on a count in WHERE, which runs before groups exist, so WHERE COUNT(*) > 5 is an error, not a filter.

Two common mistakes follow. Counting a column when you mean to count rows silently drops NULL rows. Counting rows when you mean distinct values inflates the answer whenever a value repeats. Both produce a plausible looking number. Always state the unit first: rows, non-NULL values, or distinct values.

The Sample Tables Used Throughout This Guide

Every worked example uses the same small tables, so each result can be traced back to the same source rows. A tiny dataset makes a wrong count visible, while a large dataset hides it.

Customers:

Orders:

Read the dataset as a story before you read it as data. Ana placed three orders. Ben placed one order. Cara placed no orders. Dan placed two orders. Eli placed no orders. There are five customers and six orders in total. Any correct query in this guide must reproduce those facts.

Note what is absent. Cara and Eli have no rows in the orders table at all. That absence is the whole point of the next section. A query that starts from the orders table alone cannot see Cara and Eli, because there is nothing to count for them there. A query that starts from customers and joins orders on the left can see them, provided it counts the right thing.

Step One: Count Within Each Group

Grouping turns many rows into one row per group, with an aggregate beside each group. The basic grouped count is:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

This returns one row per customer who has at least one order:

Ana has 3, Ben has 1, and Dan has 2. Cara and Eli are missing, not counted as zero. That is correct behavior for a query that starts from orders, because GROUP BY can only group rows that exist. If your question includes customers with zero orders, starting from orders is the wrong starting point.

The LEFT JOIN version keeps every customer:

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

This returns:

The choice of COUNT(o.order_id) instead of COUNT() is doing real work here. For Cara and Eli, the LEFT JOIN still produces one row each, but every orders column in those rows is NULL. COUNT() would count those rows and report 1 for a customer with no orders, which is wrong. COUNT(o.order_id) counts only rows where order_id is not NULL, so it correctly reports 0. This is the single most common count of count error, and it is worth memorizing as a rule: after a LEFT JOIN, count the column from the right hand table, not the row.

GROUP BY also has a NULL rule: rows whose grouping column is NULL fall into a single NULL group in PostgreSQL, MySQL, and SQLite, rather than each NULL forming its own group. If you need NULL rows separated or excluded, handle them explicitly before grouping.

Why COUNT(COUNT(*)) Fails

A natural next step is to write the two levels in one expression:

SELECT COUNT(COUNT(*)) FROM orders;

This fails in PostgreSQL, MySQL, and SQLite. The reason is a rule about query levels, not about COUNT in particular. An aggregate function takes rows as input and returns one value per group. A nested aggregate would need the inner aggregate to finish grouping before the outer aggregate starts, but both aggregates sit at the same query level, where only one grouping pass exists. The database therefore rejects the nesting instead of guessing which level you meant.

PostgreSQL mailing list discussion of the standard rule puts it plainly: a set function shall not contain another set function, and cases that look like nesting must be written as multiple levels of SELECT. MySQL reports the same idea as an invalid use of a group function. The fix is the same everywhere: give each aggregation its own query level.

That fix is not a workaround. It is the structure of the question: the inner query answers “how many orders per customer,” and the outer query answers “how many customers have each order count.”

A related error is putting the outer filter in the wrong place. This query is wrong:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE COUNT(*) >= 2
GROUP BY customer_id;

WHERE cannot see COUNT(*), because WHERE filters individual rows before grouping. The correct place for a condition on a grouped count is the HAVING clause for filtering groups in the same query, or the WHERE clause of an outer query that reads the grouped result. The companion HAVING article covers the same query form in detail. For count of count work, the outer query form is usually clearer, because the outer WHERE reads a named column rather than an aggregate.

Step Two: Count the Grouped Counts

Once the inner count exists as a named column, the outer query is ordinary SQL. The grouped counts from the LEFT JOIN example are 3, 1, 0, 2, and 0. Counting those values answers every reading of the phrase from the earlier section.

To count the number of distinct order counts, meaning the number of different count values rather than the number of customers, write:

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

The inner query produces five rows with counts 3, 1, 0, 2, and 0. The distinct values are 0, 1, 2, and 3, so the outer query returns 4. Always alias a derived table, as shown with per_customer. Omitting the alias is a syntax error in MySQL and in pre-16 PostgreSQL.

To count the distribution, meaning how many customers fall in each order count band, group by the inner count in the outer query:

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

This returns:

Two customers placed zero orders, and one customer each placed one, two, and three orders. The second column adds to 5, the number of customers. If it added to anything else, the query would be wrong, because every customer belongs to exactly one band.

To count only customers who meet a threshold, filter the outer query:

SELECT COUNT(*) AS customers_with_two_or_more_orders
FROM (
    SELECT COUNT(o.order_id) AS order_count
    FROM customers AS c
    LEFT JOIN orders AS o
        ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
) AS per_customer
WHERE order_count >= 2;

Ana with 3 and Dan with 2 pass the filter, so the result is 2. The WHERE clause here is legal because it filters rows of the subquery result, where order_count is already an ordinary column. The same condition would be illegal in the inner query, where order_count does not yet exist as a column.

A common table expression, written with WITH, expresses the same two levels with less nesting and is often easier to read:

WITH per_customer AS (
    SELECT COUNT(o.order_id) AS order_count
    FROM customers AS c
    LEFT JOIN orders AS o
        ON c.customer_id = o.customer_id
    GROUP BY c.customer_id
)
SELECT order_count, COUNT(*) AS customers_with_that_count
FROM per_customer
GROUP BY order_count
ORDER BY order_count;

The result is identical to the subquery distribution query. Choose the form your team reads more easily. A common table expression names the intermediate result, which helps when it is reused. Do not assume one form is faster. Measure with your own tables and indexes.

Counting Counts With Conditions Inside the Group

Sometimes the inner count itself needs a condition. For example, how many customers placed at least two orders above a value. The condition on individual rows belongs inside the inner query, in WHERE or in a conditional count, while the condition on the resulting count belongs in the outer query. Mixing those two levels is the second most common error after the LEFT JOIN mistake.

Conditional counting inside a group uses CASE WHEN, which works in all three dialects. This query counts only orders with a total of at least 30 per customer:

SELECT
    c.customer_id,
    COUNT(CASE WHEN o.total >= 30 THEN o.order_id END) AS large_order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

Ana has three orders at 40, 25, and 60, so two pass the threshold and the count is 2. Ben has one order at 15, so the count is 0. Dan has two orders at 90 and 30, so the count is 2. Cara and Eli have no orders, so the count is 0. The CASE WHEN expression returns NULL when the condition is false or when there is no order, and COUNT ignores NULL values, which is exactly the behavior needed. Writing ELSE 0 instead of leaving the ELSE out would break the query, because COUNT would then count the zeros as values. When you count conditionally with CASE WHEN, omit the ELSE or write ELSE NULL explicitly, and test the zero case by hand.

PostgreSQL and SQLite also support a FILTER clause for the same idea:

SELECT
    c.customer_id,
    COUNT(o.order_id) FILTER (WHERE o.total >= 30) AS large_order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON c.customer_id = o.customer_id
GROUP BY c.customer_id;

MySQL does not support FILTER in this form, so CASE WHEN is the portable choice. Either way, the outer count step stays the same: wrap the grouped result, then count or filter its named count column.

A Second Worked Example: Authors and Articles

A second dataset guards against learning the pattern only for customers and orders. Suppose a blog has four authors, and the articles table records one row per article with an author_id. Maya wrote 6 articles, Jon wrote 6 articles, Priya wrote 2 articles, and Sam wrote 0 articles. Sam has no rows in the articles table, so the same LEFT JOIN rule applies.

The distribution query wraps the grouped count:

SELECT article_count, COUNT(*) AS authors_with_that_count
FROM (
    SELECT COUNT(p.article_id) AS article_count
    FROM authors AS a
    LEFT JOIN articles AS p
        ON a.author_id = p.author_id
    GROUP BY a.author_id
) AS per_author
GROUP BY article_count
ORDER BY article_count;

The result is 0 with 1 author, 2 with 1 author, and 6 with 2 authors. The outer COUNT(*) counts authors per band, not articles. That sentence is worth rereading, because it is where readers slip. The inner COUNT counts articles. The outer COUNT counts authors. The column name in the outer query should say which one it counts, so a reader who only sees the outer query still knows the unit. Names such as authors_with_that_count and customers_with_that_count carry that information. A bare name such as total or count does not.

Now ask how many authors wrote at least five articles. Filter the outer result, and Maya and Jon pass, so the result is 2. The threshold uses the grouped count, not raw article rows, because crossing a threshold is a property of the grouped author, not of a single article.

The average here is 3.5, from 14 articles across 4 authors, but it hides the shape: one author at zero, one at two, and two at six. Give the distribution or the threshold count, not the average alone.

Dialect Differences You Must Label

The core pattern works identically in PostgreSQL, MySQL, and SQLite: group in an inner query, then count in an outer query. The differences sit at the edges, and each one can break a query that is copied without checking.

The first difference is the FILTER clause. PostgreSQL and SQLite support FILTER for aggregates, while MySQL does not support it in the same form. Use CASE WHEN for portable conditional counts, and use FILTER only when you know the target dialect supports it.

The second difference is COUNT with multiple DISTINCT expressions. MySQL allows COUNT(DISTINCT expr1, expr2) to count distinct combinations. PostgreSQL and SQLite do not allow that form in the same way. For portable code, use a subquery that selects distinct pairs and counts them.

The third difference is how strictly the database checks GROUP BY. MySQL in its default modern mode and PostgreSQL both require non aggregated columns in SELECT to appear in GROUP BY or inside an aggregate, with a PostgreSQL exception when grouping by a primary key. SQLite is more permissive and may accept a bare column alongside an aggregate, returning a value from an arbitrary row in the group. Portable code groups by every non aggregated column it selects.

The fourth difference is window functions. A window function such as COUNT(*) OVER (PARTITION BY customer_id) counts without collapsing rows, which is useful when you need a per group count beside each detail row. Window functions are supported in current PostgreSQL, MySQL, and SQLite, but older deployments may lack them, so check your version first. A window count is not a replacement for a count of counts, because it does not collapse groups.

The fifth difference is the return type. COUNT always returns a number, not NULL, even when no rows match. PostgreSQL returns COUNT as a bigint. Check the dialect documentation for the exact type before making strict type assumptions in application code.

Checking Your Answer Before You Trust It

A count of count query deserves three checks. First, check the totals. The outer distribution counts must add to the number of groups, and the inner counts must add to the number of rows counted. In the customer example, the distribution 2 plus 1 plus 1 plus 1 equals 5 customers, and the inner counts 3 plus 1 plus 0 plus 2 plus 0 equal 6 orders. If either total is wrong, the query is wrong, even if it runs without an error.

Second, check the zero and NULL cases by hand. Find one group that should be zero and confirm it appears as zero, not as one and not as missing. Confirm COUNT(column) excludes NULL values while COUNT(*) includes the rows. If your dataset has no zero case, invent one in a test table.

Third, check the unit at each level and write it in the column alias. Say “two customers placed two or more orders” rather than “the count is two.” On a large table, filter with WHERE before grouping when possible, index the grouping and join columns, and check the query plan. Do not add DISTINCT to an outer COUNT to fix a duplicated join. Fix the join, or aggregate before joining.

Common Errors and Their Fixes

COUNT(COUNT(*)) is rejected. The fix is a subquery or a common table expression. Only a separate query level gives the outer aggregate a finished value to count.

COUNT(*) after a LEFT JOIN reports 1 for groups with no matching rows. The fix is COUNT(column from the right hand table), which reports 0 because the column is NULL in the unmatched rows.

Filtering on a count in WHERE inside the same query level fails, because WHERE runs before grouping. Use HAVING for a same level group filter, or an outer query WHERE on the named count column for a count of count question.

Grouping by the wrong key changes the unit. Grouping orders by order_id counts orders, not customers. Grouping by customer_id counts customers. If the outer count looks too large, check the inner GROUP BY first.

GROUP BY collapses rows before the outer query sees them, so an outer query cannot recover discarded detail. If you need both the distribution and the detail, write two queries, or use a window function to carry the grouped count beside the detail rows.

COUNT(DISTINCT customer_id) answers “how many different customers,” not “how many customers have each order count.” When the question needs the distribution, write the two level query, even though it is longer.

When to Use a Simpler Query Instead

Not every counting question needs two levels. If you only need the total number of rows, use COUNT(*) with no GROUP BY. If you only need distinct values, use COUNT(DISTINCT column). If you only need counts per group, use one GROUP BY and stop. If you only need groups that pass a threshold, use GROUP BY with HAVING, the companion pattern covered in the HAVING article. Add the outer level only when the question counts the groups themselves, distributes them by their counts, or filters on a count and then counts what remains. A window function is the right alternative when you need a count beside detail rows without collapsing them.

FAQ

What does count of count mean in SQL? It means counting an earlier count: group rows, count within each group, then count those counts in an outer query, as a total, a distribution, or a filtered count.

Why does COUNT(COUNT(*)) not work? An aggregate cannot take another aggregate as its direct argument at the same query level in PostgreSQL, MySQL, or SQLite. Put the first count in a subquery or a common table expression, then count its result in the outer query.

How do I count how many customers have exactly two orders? Group orders by customer in an inner query, count orders per customer, then count the inner rows where the count equals two in the outer query. To include zero order customers, start from customers with a LEFT JOIN and count the orders column, not the row.

What is the difference between COUNT(*) and COUNT(column)? COUNT(*) counts rows, including rows where the column is NULL. COUNT(column) counts only rows where that column is not NULL. After a LEFT JOIN, that difference decides whether a group with no matches reports 1 or 0.

How do I count the number of groups? Run the grouped query as a subquery or a common table expression, then SELECT COUNT(*) from it in the outer query. For the distribution, GROUP BY the inner count in the outer query instead.

Can I use a CTE instead of a subquery for a count of counts? Yes. A WITH clause naming the grouped result, followed by an outer query that counts or groups it, gives the same answer as the subquery form. Keep aliases explicit about the unit at each level.

Does COUNT(DISTINCT column) count the counts? No. COUNT(DISTINCT column) counts distinct non-NULL values in one pass. It does not produce a count per group and then count those counts. Use the two level pattern when you need the distribution.

How do I handle customers with no orders? Start from customers, LEFT JOIN orders, and COUNT the orders column. COUNT(*) would report 1 for a customer with no orders, because the LEFT JOIN still produces one row. COUNT(order_id) reports 0.

Keep reading: Inner Join in SQL: How Matching Rows Work, With Worked Examples

Keep reading: HAVING in SQL: Filtering Grouped Results

Keep reading: SQL Queries With CASE: Conditional Logic in a Query

Sources & references

• PostgreSQL Documentation, Aggregate Functions: COUNT(*) is the number of input rows, and COUNT(expression) is the number of input rows for which the expression is not NULL.
• SQLite Documentation, Built-in Aggregate Functions: COUNT(X) returns the number of times X is not NULL in a group, and COUNT(*) returns the total number of rows in the group.
• PostgreSQL mailing list discussion of the SQL standard rule against nested set functions, explaining that cases that look like nesting must be written as multiple levels of SELECT.
• MySQL Reference Manual material on GROUP BY aggregate functions, as summarized in the MySQL 5.1 reference excerpt consulted for this article: COUNT(expr) returns the number of non-NULL values, COUNT(*) returns the number of rows retrieved, and COUNT(DISTINCT expr) returns the number of different non-NULL values.