📊 AggregationbeginnerCommonly used

How to count rows in SQL

Count rows, non-null values, or distinct values in a table.

Count all rowsAll databases

SELECT COUNT(*) AS total_users
FROM users;

COUNT(*) counts every row including NULLs.

Count non-NULL valuesAll databases

SELECT COUNT(phone) AS users_with_phone
FROM users;

COUNT(column) counts only rows where the column is not NULL.

When to use: Finding how many rows have a value in a specific column.

Count distinct valuesAll databases

SELECT COUNT(DISTINCT country) AS unique_countries
FROM users;

Counts unique non-NULL values in the column.

Count with a filterAll databases

SELECT COUNT(*) AS active_users
FROM users
WHERE active = true;

The WHERE clause filters rows before counting.

Count per groupAll databases

SELECT
  country,
  COUNT(*) AS user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

Count rows per country. GROUP BY is required when using COUNT with other columns.

Common Mistakes

→COUNT(*) vs COUNT(column) — COUNT(*) includes NULLs, COUNT(col) excludes them
→Using COUNT with LEFT JOIN — COUNT(*) counts NULL rows, use COUNT(right_table.id)