Count all rows with COUNT(*)

SELECT COUNT(*) FROM orders;

COUNT(*) counts every row, including ones with NULL values. It is the standard way to get a simple total.

Count non-NULL values

SELECT COUNT(email) FROM users;

Passing a column name counts only rows where that column is not NULL, so this tells you how many users have an email on file.

Count unique values

SELECT COUNT(DISTINCT country) FROM users;

Adding DISTINCT counts each different value once, which here returns how many countries are represented.

Count rows per group

SELECT country, COUNT(*) AS total
FROM users
GROUP BY country;

GROUP BY splits the rows into groups and counts each one, giving a count per country in a single query.

Common mistakes / tips

  • COUNT(*) vs COUNT(col). The first counts all rows; the second skips NULL values.
  • WHERE before GROUP BY. Filter individual rows with WHERE, then group what remains.
  • HAVING for group filters. Use HAVING COUNT(*) > 1 to filter on the count itself.

Frequently asked questions

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

COUNT(*) counts every row regardless of contents. COUNT(column) ignores rows where that column is NULL, so the numbers differ when a column has missing values.

How do I count rows that match a condition?

Add a WHERE clause: SELECT COUNT(*) FROM orders WHERE total > 100; counts only the rows that pass the test.

Want to practise counting and grouping on real data? Our free SQL course runs queries right in your browser.