# SQL COUNT Function: Syntax, Examples and GROUP BY

A count SQL query returns the number of rows that match a condition or belong to a group. The `COUNT` function is one of the most used aggregate functions in SQL, and it comes in three common forms: `COUNT(*)`, `COUNT(column)`, and `COUNT(DISTINCT column)`. Each answers a different question, and mixing them up is the most frequent source of wrong totals.

## Quick Answer

- `COUNT(*)` counts every row in the result set or group, including rows that contain NULL values in some columns [1].
- `COUNT(column)` counts only rows where that column is not NULL [1].
- `COUNT(DISTINCT column)` counts unique non-NULL values, because duplicates are filtered before the aggregate runs [1].
- `GROUP BY` splits the rows into groups first, then `COUNT` returns one number per group.
- `COUNT` ignores NULLs in its argument, so `COUNT(*)` and `COUNT(column)` can differ on the same table.

## Syntax

The general form is:

```sql
COUNT(*) | COUNT(expression) | COUNT(DISTINCT expression)
```

| Argument | Required? | Meaning |
|---|---|---|
| `*` | Yes, if you want all rows | Counts every row in the group, with no NULL filtering |
| `expression` | Yes, if not using `*` | Counts rows where the expression is not NULL |
| `DISTINCT` | No | Removes duplicate values before counting [1] |
| `FILTER (WHERE ...)` | No | Restricts which rows feed the aggregate [1] |
| `OVER (...)` | No | Turns `COUNT` into a window function instead of a grouped aggregate |

`COUNT` is an aggregate function, so it collapses many rows into one value unless you add `GROUP BY` or an `OVER` clause. The `DISTINCT` keyword can precede the argument in any single-argument aggregate, and duplicates are filtered before the values reach the function [1].

## How It Works

Think of the query in two stages. First, SQL decides which rows qualify, based on `FROM`, `JOIN`, and `WHERE`. Second, the aggregate runs over those rows.

Without `GROUP BY`, the whole result set is one group. `COUNT(*)` returns a single number for the entire table. With `GROUP BY customer_id`, SQL builds one group per distinct `customer_id` value and runs `COUNT` once per group.

The NULL rule is what separates the forms. `COUNT(*)` has no argument, so there is nothing to test for NULL, and it returns the total number of rows in the group [1]. `COUNT(amount)` tests each `amount` value and skips the NULLs. If a column is fully populated, the two return the same number. If it has gaps, they diverge.

`COUNT(DISTINCT customer_id)` applies the distinct filter first, so repeated customer IDs collapse to one before counting [1]. This is how you move from "how many orders" to "how many customers placed orders."

Order of operations matters for filtering. `WHERE` removes rows before grouping, so a filtered count excludes them entirely. `HAVING` filters after grouping, so it can test the count itself, for example keeping only customers with more than two orders.

## Worked Example

The dataset is a small `orders` table with eight rows, four customers, and one row per order.

| order_id | customer_id | amount |
|---|---|---|
| 1 | 101 | 24.50 |
| 2 | 102 | 89.99 |
| 3 | 101 | 15.00 |
| 4 | 103 | 42.75 |
| 5 | 102 | 60.00 |
| 6 | 101 | 7.25 |
| 7 | 104 | 130.40 |
| 8 | 102 | 18.60 |

The first query counts all rows and all unique customers. The second groups by customer.

```sql
SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT customer_id) AS distinct_customers
FROM orders;

SELECT customer_id,
       COUNT(*) AS orders_per_customer
FROM orders
GROUP BY customer_id
ORDER BY orders_per_customer DESC, customer_id;
```

The first statement returns `total_orders = 8` and `distinct_customers = 4`. The second returns one row per customer:

| customer_id | orders_per_customer |
|---|---|
| 101 | 3 |
| 102 | 3 |
| 103 | 1 |
| 104 | 1 |

The result was checked with an equivalent SQLite query. `COUNT(*)` counts every row, so it returns 8. `COUNT(DISTINCT customer_id)` collapses the repeated IDs 101 and 102 down to single values, so it returns 4. Inside the grouped query, `COUNT(*)` runs once per customer group, giving 3, 3, 1, and 1. The `ORDER BY` sorts the largest groups first, and `customer_id` breaks the tie between the two customers with three orders each.

## More Examples

**Count rows matching a condition.** Filter before counting so the aggregate only sees qualifying rows.

```sql
SELECT COUNT(*) AS big_orders
FROM orders
WHERE amount >= 50;
```

**Count non-NULL values in one column.** If some rows had a missing `amount`, this would return fewer rows than `COUNT(*)`.

```sql
SELECT COUNT(amount) AS orders_with_amount
FROM orders;
```

**Filter groups after counting.** `HAVING` tests the aggregate, which `WHERE` cannot do.

```sql
SELECT customer_id, COUNT(*) AS orders_per_customer
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;
```

**Count with a FILTER clause.** This restricts the rows feeding the aggregate without changing the grouping [1].

```sql
SELECT customer_id,
       COUNT(*) FILTER (WHERE amount >= 50) AS big_orders
FROM orders
GROUP BY customer_id;
```

**Count distinct values across a group.** Useful when a group can contain repeated values.

```sql
SELECT COUNT(DISTINCT customer_id) AS customers_with_orders
FROM orders;
```

If you work in spreadsheets as well, the same counting logic appears in the [COUNT function in Excel](/blog/data-analysis/count-function-in-excel), where `COUNTA` and `COUNTIF` play roles similar to `COUNT(*)` and `COUNT` with a filter.

## Errors and How to Fix Them

**Column not in GROUP BY.** Selecting a column that is neither grouped nor aggregated raises an error in strict databases. Add the column to `GROUP BY` or wrap it in an aggregate.

**Aggregate in WHERE.** `WHERE COUNT(*) > 1` fails because `WHERE` runs before grouping. Move the condition to `HAVING`.

**Unexpectedly low count.** If `COUNT(column)` returns less than `COUNT(*)`, the column contains NULLs. Switch to `COUNT(*)` if you want every row.

**Wrong distinct count.** `COUNT(DISTINCT a, b)` is not supported in every database. Count a single expression, or concatenate the columns first.

**Nested aggregates.** `COUNT(COUNT(*))` is invalid. Use a subquery or a window function instead. The [subqueries in SQL](/blog/data-analysis/subqueries-in-sql-types-examples) guide covers how to wrap one aggregate inside another query.

## Common Mistakes

- **Using `COUNT(column)` when you mean `COUNT(*)`.** The column form skips NULLs, so totals come out low. Use `COUNT(*)` when you want the row count.
- **Forgetting `GROUP BY`.** Without it, the query returns one row for the whole table instead of one row per group.
- **Putting the aggregate in `WHERE`.** `WHERE` cannot see aggregates. Move the test to `HAVING`.
- **Assuming `COUNT(DISTINCT)` counts NULLs.** It does not. NULL values are excluded before the distinct filter runs [1].
- **Counting after a join that multiplies rows.** A one-to-many join inflates the row count. Count distinct IDs or aggregate in a subquery first.
- **Expecting a stable row order.** Aggregates return values, not ordered rows. Add `ORDER BY` if the order matters.

## Limitations

`COUNT` tells you how many, never which. It cannot show the rows behind a number, so a count of 3 does not reveal whether those three orders were large or small. Pair it with `SUM`, `AVG`, or `MIN` to describe the group. The [SQL AVG function](/blog/data-analysis/sql-avg-function-syntax-examples) and [SQL MAX function](/blog/data-analysis/sql-max-function-syntax-examples) pages show how those aggregates combine with the same `GROUP BY` structure.

`COUNT` also hides NULL problems. A column that is 90 percent empty still produces a clean-looking number from `COUNT(*)`, and nothing in the output warns you. Check `COUNT(*)` against `COUNT(column)` for the same column to expose gaps. Finally, `COUNT(DISTINCT ...)` costs more than a plain count on large tables, because the database must track unique values. On very large datasets, that extra work is measurable.

## Frequently Asked Questions

### What is the difference between COUNT(*) and COUNT(1)?

They behave the same in practice. `COUNT(1)` counts a constant expression that is never NULL, so it returns the same number as `COUNT(*)`. `COUNT(*)` is the clearer choice because it states the intent directly.

### Does COUNT(*) include NULL values?

Yes. `COUNT(*)` has no argument to test, so it counts every row in the group, including rows with NULLs in other columns [1]. Only the argument form, such as `COUNT(amount)`, filters out NULLs.

### Can I use COUNT with GROUP BY and ORDER BY together?

Yes. `GROUP BY` builds the groups, `COUNT` returns one value per group, and `ORDER BY` sorts the output. You can order by the count itself, as in `ORDER BY COUNT(*) DESC`, or by an alias you defined in the select list.

### Why does my COUNT return fewer rows than expected?

The most common causes are a `WHERE` clause that filters more than you intended, a join that drops unmatched rows, or `COUNT(column)` skipping NULLs. Run the same query with `COUNT(*)` and without the filter to isolate which one is responsible.

### How do I count unique values in SQL?

Use `COUNT(DISTINCT column)`. The distinct filter removes duplicate values before the count runs, so you get the number of unique non-NULL values [1]. For counts within groups, combine it with `GROUP BY`, and for ranked output alongside those counts, see the [SQL RANK function](/blog/data-analysis/sql-rank-function) and [SQL ROW_NUMBER function](/blog/data-analysis/sql-row-number-function) guides.

## References

1. [Built-in Aggregate Functions](https://www.sqlite.org/lang_aggfunc.html)

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)
- [PostgreSQL Tutorial: The SQL Language](https://www.postgresql.org/docs/current/tutorial-sql.html)

## Related Articles

- [COUNT Function in Excel: Syntax, Examples and Tips](/blog/data-analysis/count-function-in-excel)
- [SQL ROW_NUMBER Function: Syntax and Examples](/blog/data-analysis/sql-row-number-function)
- [SQL AVG Function: Syntax, Examples and Grouped Averages](/blog/data-analysis/sql-avg-function-syntax-examples)
- [SQL RANK Function: Syntax, PARTITION BY and Examples](/blog/data-analysis/sql-rank-function)
- [SQL LEAD Function: Syntax and Examples](/blog/data-analysis/sql-lead-function)