MySQL count() Function

Last updated: October 19, 2026Oct 19, 2026 4 min read

COUNT() answers "how many?": how many tickets are open, how many customers we have, how many different cities they come from. It has three forms that behave differently with NULLs and duplicates, and choosing the wrong one is a common source of wrong numbers.

1. The Three Forms of COUNT

Table: tickets (7 rows)

SELECT COUNT(*) AS all_rows, COUNT(city) AS with_city, COUNT(DISTINCT city) AS distinct_cities, COUNT(agent) AS assigned FROM tickets;

Result: 1 row

2. Counting with WHERE

SELECT COUNT(*) AS open_tickets FROM tickets WHERE status = 'open';

Result: 1 row

SELECT COUNT(DISTINCT customer) AS customers_with_high_priority FROM tickets WHERE priority = 'high';

Result: 1 row

3. Counting per Group

SELECT status, COUNT(*) AS tickets FROM tickets GROUP BY status ORDER BY tickets DESC;

Result: 3 rows

SELECT city, COUNT(*) AS tickets FROM tickets GROUP BY city ORDER BY city;

Result: 4 rows

Rows with a NULL city form their own group, shown first.

4. Conditional Counts in One Query

Count several things at once with SUM(condition) (a condition is 1 or 0) or COUNT(CASE … END) (counts non-NULLs):

SELECT COUNT(*)                                      AS total,
       SUM(status = 'open')                           AS open_cnt,
       SUM(priority = 'high')                         AS high_cnt,
       COUNT(CASE WHEN status = 'open' AND priority = 'high' THEN 1 END) AS urgent_open,
       SUM(agent IS NULL)                             AS unassigned
FROM tickets;

Result: 1 row

SELECT agent,
       COUNT(*)              AS total,
       SUM(status = 'open')  AS open_now,
       SUM(status = 'closed') AS closed
FROM tickets
WHERE agent IS NOT NULL
GROUP BY agent;

Result: 2 rows

5. Showing Zero Counts with LEFT JOIN

Tanya has no tickets, so a plain GROUP BY on tickets never shows her. Start from agents and LEFT JOIN, counting a column from the right table:

SELECT a.agent, COUNT(t.ticket_id) AS tickets
FROM agents a
LEFT JOIN tickets t ON t.agent = a.agent
GROUP BY a.agent
ORDER BY tickets DESC;

Result: 3 rows

6. Filtering on a Count

SELECT customer, COUNT(*) AS tickets FROM tickets GROUP BY customer HAVING COUNT(*) > 1;

Result: 2 rows

7. COUNT as a Window Function

SELECT ticket_id, customer, opened,
       COUNT(*) OVER (PARTITION BY customer) AS customer_total,
       COUNT(*) OVER (ORDER BY opened)       AS running_total
FROM tickets
ORDER BY opened, ticket_id;

Result: 7 rows

8. Counting Big Tables Quickly

• COUNT(*) and COUNT(1) are identical in MySQL; neither is faster.
• InnoDB doesn't store an exact row count (because of MVCC), so COUNT(*) scans an index. MySQL picks the smallest index, and from 8.0.14 can scan in parallel.
• For a quick estimate use information_schema.tables.table_rows or SHOW TABLE STATUS; it can be off by 40–50%.
• For dashboards on huge tables, keep counters in a summary table.

SELECT table_rows AS estimate FROM information_schema.tables WHERE table_schema = 'support' AND table_name = 'tickets';

Result: 1 row

9. Practice Exercises

SELECT opened, COUNT(*) AS tickets FROM tickets GROUP BY opened ORDER BY opened;

SELECT city, COUNT(*) AS tickets, COUNT(DISTINCT customer) AS customers
FROM tickets WHERE city IS NOT NULL GROUP BY city;

10. Summary

• COUNT(*) counts rows; COUNT(col) counts non-NULL values; COUNT(DISTINCT col) counts different values.
• SUM(condition) counts matching rows, several conditions in one query.
• LEFT JOIN + COUNT(right_column) shows groups with zero.
• COUNT(*) on InnoDB scans an index; use estimates or summary tables for huge tables.