Lesson Description
Build more reliable KPIs with COUNT(DISTINCT ...) and FILTER-based conditional aggregation. This lesson helps you avoid inflated counts and produce metrics stakeholders can trust.
Lesson 04
Lesson Description
Build more reliable KPIs with COUNT(DISTINCT ...) and FILTER-based conditional aggregation. This lesson helps you avoid inflated counts and produce metrics stakeholders can trust.
Easy Project
Mini project: Create a hiring shortlist query that filters by role, salary range, and hire date with deterministic ordering.
You now know how to count rows, group them and filter the groups. Two questions still come up constantly that the basic tools handle awkwardly.
The first is "how many different things?" The store has 50 orders, but how many customers placed them? Counting rows gives you 50, which is the wrong answer.
The second is "how many of this kind versus that kind, side by side?" A clinic manager wants one row per month showing total, completed, scheduled and missed appointments in separate columns. With only WHERE, you would need a separate query for each status.
COUNT(DISTINCT ...) solves the first problem, and the FILTER clause solves the second. Together they let you build compact, report-ready summaries in a single query.
-- Count unique non-NULL values
COUNT(DISTINCT expression)
-- Restrict any aggregate to rows matching a condition
AGGREGATE_FUNCTION(expression) FILTER (WHERE condition)
DISTINCT goes inside the parentheses. FILTER goes after the closing parenthesis of the aggregate and before any AS alias. You can use both at once: COUNT(DISTINCT patient_id) FILTER (WHERE status = 'scheduled').
| Expression | What it counts |
|---|---|
COUNT(*) | Every row |
COUNT(col) | Rows where col is not NULL |
COUNT(DISTINCT col) | Distinct non-NULL values of col |
COUNT(*) FILTER (WHERE cond) | Rows where cond is true |
How many orders are there, and how many distinct customers placed them?
SELECT
COUNT(*) AS orders,
COUNT(customer_id) AS orders_with_customer,
COUNT(DISTINCT customer_id) AS distinct_buyers
FROM orders;
The answer is 50, 50, 20. Every order has a customer, so the first two numbers match. But many customers ordered more than once, so only 20 distinct IDs appear. The customers table has 22 rows, which tells you two customers have never ordered anything.
The same pattern works on any column. SELECT COUNT(DISTINCT country) FROM customers returns 11: the store has 22 customers spread over 11 countries.
Which doctors see the same patients repeatedly, and which see a wide variety?
SELECT
doctor_id,
COUNT(*) AS appointments,
COUNT(DISTINCT patient_id) AS unique_patients
FROM appointments
GROUP BY doctor_id
ORDER BY appointments DESC, doctor_id
LIMIT 5;
The top five rows are doctors 1, 3, 5 and 11 with 8 appointments each, then doctor 9 with 7. Look closer: doctor 1 had 8 appointments but only 4 unique patients, so the average patient came back twice. Doctor 5 also had 8 appointments, but across 6 different people. The plain count alone would have made those two doctors look identical.
Now the monthly appointment breakdown, one column per outcome:
SELECT
DATE_TRUNC('month', appointment_date)::date AS month,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE status = 'completed') AS completed,
COUNT(*) FILTER (WHERE status = 'scheduled') AS scheduled,
COUNT(*) FILTER (WHERE status IN ('cancelled', 'no-show')) AS missed
FROM appointments
GROUP BY month
ORDER BY month;
| month | total | completed | scheduled | missed |
|---|---|---|---|---|
| 2025-01-01 | 5 | 4 | 0 | 1 |
| 2025-02-01 | 11 | 10 | 0 | 1 |
| 2025-03-01 | 14 | 14 | 0 | 0 |
| 2025-04-01 | 21 | 13 | 7 | 1 |
| 2025-05-01 | 15 | 2 | 13 | 0 |
Five rows, one per month. Each FILTER acts like a private WHERE for its own aggregate: it decides which rows of the current group get counted. The rows themselves are not removed, which is why total still sees everything. You can read the story straight off the table: the past months are mostly completed, and the future is mostly scheduled.
Doing this with WHERE would be impossible in one query, because a WHERE that keeps only completed rows would also throw away the scheduled ones.
FILTER is not limited to COUNT. Do people hired earlier earn more than recent hires?
SELECT
ROUND(AVG(salary), 2) AS overall_avg,
ROUND(AVG(salary) FILTER (WHERE hire_date < '2005-01-01'), 2) AS hired_before_2005,
ROUND(AVG(salary) FILTER (WHERE hire_date >= '2005-01-01'), 2) AS hired_2005_or_later
FROM employees;
Result: 8325.00 overall, 10825.00 for the 12 people hired before 2005, and 7253.57 for the 28 hired since. Three averages over three different subsets, all from one scan of the table.
Which doctors with a meaningful caseload (at least 5 appointments) complete the highest share of their visits?
SELECT
doctor_id,
COUNT(*) AS appointments,
COUNT(*) FILTER (WHERE status = 'completed') AS completed,
ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'completed') / COUNT(*), 1) AS completion_pct
FROM appointments
GROUP BY doctor_id
HAVING COUNT(*) >= 5
ORDER BY completion_pct DESC, doctor_id;
Six doctors pass the HAVING filter. Doctor 9 leads at 85.7 percent (6 of 7), followed by doctors 5 and 11 at 75.0. Notice the 100.0 at the front of the calculation. It matters, as you will see in the mistakes section.
This also fixes the subtle issue from the previous lesson: because the status condition lives in FILTER instead of WHERE, COUNT(*) in HAVING sees all of each doctor's appointments.
DISTINCT ignores NULLs. COUNT(DISTINCT col) counts distinct non-NULL values. If a column contains NULLs, they contribute nothing, not even a single "NULL" value. This differs from GROUP BY, which does give NULL its own group (you saw that with manager_id in the GROUP BY lesson).
DISTINCT has a cost. To count distinct values, PostgreSQL has to sort or hash them to spot duplicates. On these small sandboxes it is instant, but on millions of rows COUNT(DISTINCT ...) is noticeably slower than COUNT(*).
FILTER is evaluated per row, inside the aggregate. For each input row, PostgreSQL checks the FILTER condition and only feeds the row into that aggregate if the condition is true. A NULL or false result means "skip." It is equivalent to the classic CASE trick:
COUNT(*) FILTER (WHERE status = 'completed')
-- same result as
COUNT(CASE WHEN status = 'completed' THEN 1 END)
-- and as
SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END)
All three return 43 on the appointments table. FILTER states the intent most clearly. You will still see the CASE forms often, because they work in every database.
SELECT COUNT(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed
FROM appointments;
This returns 66, not 43. COUNT counts non-NULL values, and 0 is not NULL, so every row is counted. Either drop the ELSE (so non-matches become NULL), switch to SUM, or simply use FILTER.
SELECT doctor_id,
COUNT(*) FILTER (WHERE status = 'completed') / COUNT(*) AS completion_rate
FROM appointments
GROUP BY doctor_id;
Every row shows 0. Both counts are bigint, and integer division in PostgreSQL throws away the remainder, so 5 / 8 becomes 0. Multiply by 100.0 (or cast one side to numeric) before dividing, as in example 5.
SELECT COUNT(*) AS completed FILTER (WHERE status = 'completed')
FROM appointments;
ERROR: syntax error at or near "FILTER"
FILTER is part of the aggregate call, so it must come before the alias: COUNT(*) FILTER (WHERE ...) AS completed.
COUNT(DISTINCT customer_id) answers "how many customers," not "how many orders." Ask yourself which noun the question is about before choosing between COUNT(*) and COUNT(DISTINCT ...).
| Feature | PostgreSQL | SQLite | MySQL | SQL Server |
|---|---|---|---|---|
COUNT(DISTINCT col) | Yes | Yes | Yes | Yes |
FILTER (WHERE ...) | Yes | Yes (3.30 and later) | No | No |
CASE inside aggregate | Yes | Yes | Yes | Yes |
On MySQL and SQL Server, use SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT(CASE WHEN ... THEN 1 END) to get the same result.
The exercises below ask you to count how many distinct departments are actually staffed, report shipped and cancelled order counts side by side in a single row, and build a per-department summary that shows total headcount next to the number of higher earners. The last two explicitly ask for FILTER, so resist the urge to fall back to CASE, and remember that FILTER comes before the alias.
COUNT(DISTINCT col) counts unique non-NULL values; use it when the question is about distinct entities, not rows.aggregate FILTER (WHERE condition) restricts one aggregate to matching rows without removing rows from the rest of the query.FILTER plus GROUP BY builds pivot-style reports with one column per category.FILTER works with SUM, AVG, MIN, MAX and can be combined with DISTINCT.ELSE 0 inside COUNT(CASE ...).Counting is subtle: rows, non-NULL values and unique values are different numbers. COUNT(DISTINCT) answers "how many different customers ordered", and conditional aggregates let one query return several segmented counts side by side, the shape most dashboards need.
Orders versus unique customers
SELECT COUNT(*) AS orders,
COUNT(DISTINCT customer_id) AS customers
FROM orders;A customer with ten orders counts ten times in the first column and once in the second.
Status breakdown per doctor
SELECT doctor_id,
COUNT(*) AS total,
COUNT(*) FILTER (WHERE status = 'completed') AS completed,
COUNT(*) FILTER (WHERE status = 'no-show') AS no_shows
FROM appointments
GROUP BY doctor_id
ORDER BY total DESC;FILTER applies a condition to a single aggregate, giving one row per doctor with several counts.
COUNT(*) is 6 (one per line item) and COUNT(DISTINCT o.id) is 3 (one per order).
Concept check
3 quick questions. One at a time. Instant score at the end.
Return a single value unique_departments โ the number of distinct department_id values across all employees.
No results yet
Run a query to see results here
From the store sandbox, return two columns in a single query: shipped (orders with status 'shipped') and cancelled (status 'cancelled'). Use FILTER.
No results yet
Run a query to see results here
Return department_id, total employees, and high_earners (those with salary > 8000) per department. Use FILTER. Order by department_id.
No results yet
Run a query to see results here
Final checkpoint
Pass mark: 80%. Review each explanation after you submit.
1. Which clause should you use to filter grouped results?
2. What is the safest way to avoid accidental row multiplication in joins?
3. Which feature is best for ranking rows within partitions?
4. Why should final analytical queries usually include ORDER BY?
5. When query logic gets complex, what improves readability most?