SQL COUNT() Function — COUNT(*), COUNT(column), DISTINCT

How COUNT Works

Rows collapse into one number

Unlike SELECT which returns detail rows, COUNT scans matching rows and returns a single scalar value.

Schema Context

users ↔ orders ER

users samplejson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

orders samplejson

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

COUNT(*) — All Rows

Total ordersjson

1SELECT COUNT(*) AS order_count FROM orders;

Expected outputjson

1| order_count |
2|---|
3| 3 |

Total usersjson

1SELECT COUNT(*) AS user_count FROM users;

Expected outputjson

1| user_count |
2|---|
3| 3 |

COUNT(column) — Ignore NULLs

Form	Counts	Use when
COUNT(*)	Every row	Total row count regardless of NULLs
COUNT(column)	Rows where column IS NOT NULL	Column may be optional/nullable
COUNT(DISTINCT column)	Unique non-NULL values	How many different values exist

NULL behavior

If a column contains NULL, COUNT(column) skips those rows. COUNT(*) still counts the row.

Distinct customers who placed ordersjson

1SELECT COUNT(DISTINCT customer_id) AS unique_customers FROM orders;

Expected outputjson

1| unique_customers |
2|---|
3| 2 |

Alice (customer_id 1) has two orders; Carol (customer_id 3) has one — two unique customers total.

COUNT with WHERE

Paid orders onlyjson

1SELECT COUNT(*) AS paid_orders
2FROM orders
3WHERE status = 'paid';

Expected outputjson

1| paid_orders |
2|---|
3| 1 |

Active users onlyjson

1SELECT COUNT(*) AS active_users
2FROM users
3WHERE is_active = TRUE;

Expected outputjson

1| active_users |
2|---|
3| 2 |

Orders over $20json

1SELECT COUNT(*) AS large_orders
2FROM orders
3WHERE total_amount > 20;

Expected outputjson

1| large_orders |
2|---|
3| 1 |

COUNT with JOIN (Preview)

After joining tables, COUNT still returns one number — but over the joined result set. For per-user counts, use GROUP BY (next lesson after CASE).

Total order rows when joined to usersjson

1SELECT COUNT(*) AS joined_rows
2FROM users u
3INNER JOIN orders o ON u.user_id = o.customer_id;

Expected outputjson

1| joined_rows |
2|---|
3| 3 |

Next step

To get one count per user, combine COUNT with GROUP BY — covered in GROUP BY & Aggregates.

Common Mistakes

Expecting COUNT to return detail rows — it always returns one row (unless grouped).
Using COUNT(column) when you mean all rows — use COUNT(*) instead.
Forgetting that COUNT(DISTINCT col) ignores duplicate values.
Mixing non-aggregated columns with COUNT without GROUP BY.

Practice Prompts

How many users are inactive (is_active = FALSE)?
How many orders have status shipped or pending?
How many distinct order statuses exist in the orders table?

Try These Tools

SQL Query Explainer

Step through COUNT queries

Open tool

SQL Relationship Viewer

Visualize users ↔ orders schema

Open tool

Continue Learning

WHERE — Filtering Rows

Filter before counting

GROUP BY & Aggregates

COUNT per group

Frequently Asked Questions

What does COUNT() return?

A single number — the count of rows or non-NULL values. It never returns multiple rows unless combined with GROUP BY.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts every row. COUNT(column) counts rows where that column is NOT NULL.

Does COUNT(*) include NULL columns?

Yes. COUNT(*) counts rows regardless of NULL values in any column.

Can I use COUNT without GROUP BY?

Yes. COUNT over the whole table (optionally with WHERE) returns one summary row.

What is COUNT(DISTINCT column)?

Counts unique non-NULL values in the column — useful for "how many different customers placed orders?"