COUNT DISTINCT

COUNT(DISTINCT column) counts unique values of that column within each group, rather than counting every row.

Source data — Pattern 5: Grouping

All queries run against the shared employees table (see schema.sql). This pattern needs two new columns — city (topic 30) and job_role (topic 31) — and enough rows in one department to actually cross a "more than 5" threshold (topic 29), so IT grows from 2 employees to 7.

COUNT DISTINCT — source table 1

What it does

COUNT(DISTINCT column) counts unique values of that column within each group, rather than counting every row. A department can have several employees but only one or two distinct job roles among them — this is how you'd tell the difference.

Problem

Find unique job roles in each department.

SELECT department_id,
       COUNT(DISTINCT job_role) AS unique_roles
FROM employees
GROUP BY department_id;

Result

COUNT DISTINCT — result 1