COUNT(DISTINCT) and Distinct Aggregates

Every real table contains duplicates. Fifteen employees share only eleven job titles; five departments sit in just three cities; eight projects spread across only three status labels. When a manager asks "how many roles do we have?" she does not want a headcount — she wants the duplicates removed before anyone counts. A plain COUNT(*) would waste her time with inflated numbers.

COUNT(DISTINCT column) does exactly that. It scans the column, discards duplicate values, skips NULLs, and returns how many unique values remain. The pattern is the same for any aggregate: wrap the column in DISTINCT and duplicates collapse before the function runs.

This lesson covers counting unique values, using DISTINCT inside other aggregates such as SUM and AVG, and packing several distinct counts into a single summary row. By the end you will be able to answer questions like "how many different cities do we operate in?" and "how many unique budget sizes exist across all projects?" in a single query.

flowchart LR
  A["Engineer<br/>Engineer<br/>Marketer<br/>Sales Rep<br/>Sales Rep"] -->|COUNT| B["5 rows"]
  A -->|COUNT DISTINCT| C["3 unique<br/>roles"]
  style B fill:#b45309,color:#fff
  style C fill:#0e7490,color:#fff

COUNT(DISTINCT) removes duplicates before the aggregate runs.

Syntax and behaviour

Place DISTINCT immediately inside the parentheses, before the column name: COUNT(DISTINCT role). The database gathers every non-NULL value from that column, removes duplicates, and counts what is left. NULLs are silently dropped, just as they are with COUNT(column). If every value in the column is unique, COUNT(DISTINCT) returns the same number as COUNT(column); if many values repeat, the gap between the two numbers tells you how much duplication exists.

The same keyword works inside SUM, AVG, MIN, and MAX, though in practice you will almost always pair it with COUNT. SUM(DISTINCT salary) adds each unique salary once, which is rarely what you want for a payroll total but is useful when a value has been accidentally duplicated by a join and you need to de-duplicate before summing.

Compare total rows with distinct roles and distinct departments.

SELECT
  COUNT(*) AS total_employees,
  COUNT(DISTINCT role) AS distinct_roles,
  COUNT(DISTINCT department_id) AS distinct_depts
FROM employees;

DISTINCT in AVG and SUM

AVG(DISTINCT salary) averages each unique salary exactly once, producing a different — and usually larger — figure than AVG(salary) because duplicate mid-range values are removed from both the numerator and the denominator. Because DISTINCT collapses values first, the aggregate always sees a smaller set than it would without it. That means COUNT(DISTINCT x) can never exceed COUNT(x), and will usually be far smaller.

flowchart TB
  A["15 employees"] --> B["COUNT(*) → 15"]
  A --> C["COUNT(role) → 15<br/>counts every row"]
  A --> D["COUNT(DISTINCT role) → 11<br/>collapses duplicates first"]
  style B fill:#0e7490,color:#fff
  style C fill:#b45309,color:#fff
  style D fill:#0e7490,color:#fff

Plain COUNT sees every row; COUNT(DISTINCT) sees only unique values, and both skip NULLs.

Comparing scopes

You can scope a distinct count with WHERE just like any other aggregate. COUNT(DISTINCT role) answers "how many roles exist company-wide?" while the same expression with WHERE department_id = 1 answers "how many roles exist in Engineering?" The filter runs first, removing rows that do not match, and DISTINCT then collapses whatever values survive.

This order — filter, then collapse, then count — is fixed. You cannot ask DISTINCT to ignore certain rows on its own; you must use WHERE to remove them before the aggregate sees them. Keeping that pipeline in mind prevents the common mistake of trying to combine DISTINCT with conditional logic inside the same expression.

Multiple distinct counts in one query against the projects table.

SELECT
  COUNT(*) AS total_projects,
  COUNT(DISTINCT department_id) AS distinct_depts,
  COUNT(DISTINCT status) AS distinct_statuses
FROM projects;

When duplicates come from joins

A join can manufacture duplicates that were not in the original table. Join every employee to their department and you still have one row per employee. But join departments to projects and Engineering appears three times because it owns three projects. If you then count cities, COUNT(d.city) sees Oslo three times and returns 8 instead of 3.

That is the moment COUNT(DISTINCT d.city) saves the summary. It collapses the duplicated Oslo values back to one before the count happens. Any time your aggregate looks unexpectedly large after a join, ask whether DISTINCT is missing.

A join multiplies rows; DISTINCT keeps the count accurate.

SELECT
  COUNT(*) AS row_count,
  COUNT(DISTINCT d.name) AS distinct_depts,
  SUM(p.budget) AS total_budget
FROM departments d
JOIN projects p ON d.id = p.department_id;

Counting distinct combinations

Sometimes a single column is not specific enough. You might need to know how many unique department-and-role pairs exist, or how many different status-and-budget combinations appear among projects. You can feed an expression to COUNT(DISTINCT), not just a bare column: COUNT(DISTINCT department_id || '-' || role) treats each concatenated pair as a single value and removes duplicates from that combined list.

This technique is powerful but easy to overuse. If you find yourself concatenating many columns, consider whether a grouped query with GROUP BY would be clearer. DISTINCT is for collapsing duplicates inside an aggregate; GROUP BY is for splitting results into separate rows. Choosing the right tool keeps your queries readable and fast.

flowchart LR
  A["All rows"] --> W["WHERE<br/>filter rows"]
  W --> D["DISTINCT<br/>remove duplicates"]
  D --> C["COUNT<br/>return total"]
  style W fill:#0e7490,color:#fff
  style D fill:#0e7490,color:#fff
  style C fill:#0e7490,color:#fff

WHERE removes rows first; DISTINCT then collapses the survivors before the aggregate runs.

Exercise

How many distinct roles exist in the company? Return a single column named distinct_roles.

SELECT
  -- count unique roles here
FROM employees;

SELECT COUNT(DISTINCT role) AS distinct_roles FROM employees;

Exercise

Return one row with two columns: total_projects (the total number of projects) and distinct_departments (the number of different departments that have projects).

SELECT
  -- two aggregates here
FROM projects;

SELECT COUNT(*) AS total_projects, COUNT(DISTINCT department_id) AS distinct_departments FROM projects;

Exercise

This query was meant to count how many different cities the company has offices in, but it returns 5 — the total number of departments. Fix the aggregate so it counts unique cities.

SELECT COUNT(name) AS distinct_cities
FROM departments;

SELECT COUNT(DISTINCT city) AS distinct_cities FROM departments;

Exercise

Among employees who earn more than 500000, how many distinct departments are represented? Return one column named distinct_departments.

SELECT
  -- count unique departments among high earners
FROM employees
-- add your filter here
;

SELECT COUNT(DISTINCT department_id) AS distinct_departments FROM employees WHERE salary > 500000;

Exercise

Return one row with three columns: distinct_roles (unique roles in the company), distinct_departments (unique departments that have employees), and total_employees (the overall headcount).

SELECT
  -- three independent aggregates here
FROM employees;

SELECT COUNT(DISTINCT role) AS distinct_roles, COUNT(DISTINCT department_id) AS distinct_departments, COUNT(*) AS total_employees FROM employees;

Recap

COUNT(DISTINCT column) removes duplicate values before counting, and skips NULLs like any other aggregate.
DISTINCT works inside any aggregate, though COUNT is by far the most common partner.
COUNT(DISTINCT *) is invalid — you must name a specific column.
Multiple distinct counts can share one SELECT list, each collapsing its own column independently.
Combine DISTINCT with WHERE to count unique values inside a subset of rows.

Next you will learn to build aggregates that include only rows meeting a specific condition, using CASE expressions to categorise before you count.

COUNT(DISTINCT) and Distinct Aggregates

Syntax and behaviour

DISTINCT in AVG and SUM

Comparing scopes

When duplicates come from joins

Counting distinct combinations

Recap

Checkpoint quiz

Go deeper — technical resources