MySQL COUNT() Function

MySQL COUNT() Function: Counting Rows and Non-NULL Values

The MySQL COUNT() function counts the number of rows or non-NULL column values returned by a query. Understanding the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column) prevents subtle reporting bugs.

Three Ways to Call COUNT()

• COUNT(*): Counts every matching row, even if every column in a row is NULL.
• COUNT(column_name): Counts only rows where column_name IS NOT NULL.
• COUNT(DISTINCT column_name): Counts unique non-NULL values in that column.

Practical SQL Examples

The query below counts total rows, shipped rows (ignoring NULL timestamps), and unique buyers in demo_orders:

SELECT
    COUNT(*) AS total_orders,
    COUNT(shipped_at) AS shipped_orders,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM demo_orders;

You can compute conditional counts in one table scan by combining COUNT() with CASE WHEN (returning NULL when false so COUNT() skips it):

SELECT
    COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_count,
    COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded_count
FROM demo_orders;

Common Mistakes and Best Practices

• Do Not Write COUNT(CASE WHEN ... THEN 1 ELSE 0 END): Returning 0 instead of NULL makes COUNT() count the 0 too! Either omit ELSE so it defaults to NULL, or use SUM(CASE WHEN ... THEN 1 ELSE 0 END).
• Avoid COUNT(*) on Huge Tables for Exact Pagination: InnoDB scans index pages to compute exact counts. For infinite scroll UIs, fetch LIMIT 21 instead of 20 to check if a next page exists without counting millions of rows.

Frequently Asked Questions

Is COUNT(1) faster than COUNT(*) in MySQL InnoDB?

No, InnoDB treats COUNT(*) and COUNT(1) identically and automatically picks the smallest secondary index to scan.

What does COUNT(column) return when every row is NULL?

It returns 0 (never NULL), unlike SUM() or AVG() which return NULL when given zero non-null values.

How do I count rows per status?

Add GROUP BY status after FROM demo_orders and select status, COUNT(*) AS row_count.