Skip to main content
0%

COUNT DISTINCT and FILTER

0/3 solved3 to unlock next
Prevโ˜… Final lesson

Lesson 04

COUNT DISTINCT and FILTER

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.

Syntax

-- 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').

ExpressionWhat 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

Worked examples

1. Rows versus distinct values

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.

2. COUNT DISTINCT inside groups

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.

3. A pivot report with FILTER

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;
monthtotalcompletedscheduledmissed
2025-01-015401
2025-02-01111001
2025-03-01141400
2025-04-01211371
2025-05-01152130

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.

4. FILTER works with every aggregate

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.

5. Ratios, combined with HAVING

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.

Under the hood

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.

Common mistakes

Adding ELSE 0 inside COUNT(CASE ...)

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.

Integer division in ratios

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.

Putting FILTER in the wrong place

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.

Expecting DISTINCT to count rows

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 ...).

How other databases differ

FeaturePostgreSQLSQLiteMySQLSQL Server
COUNT(DISTINCT col)YesYesYesYes
FILTER (WHERE ...)YesYes (3.30 and later)NoNo
CASE inside aggregateYesYesYesYes

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.

Practice

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.

Key takeaways

  • 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.
  • Watch for integer division in ratios and for ELSE 0 inside COUNT(CASE ...).

COUNT DISTINCT and FILTER: in depth

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.

Worked examples

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.

Common mistakes

  • Using COUNT(DISTINCT) after a join that multiplies rows, then trusting other SUMs in the same query. Only the distinct count is protected.
  • COUNT(DISTINCT a, b) is not valid in PostgreSQL. Count distinct pairs with COUNT(DISTINCT (a, b)) or a subquery.
  • COUNT(DISTINCT) on large tables is expensive. Pre-aggregate in a CTE when you need it for many groups.

Other databases

  • The FILTER clause is PostgreSQL and SQLite; elsewhere use SUM(CASE WHEN ... THEN 1 ELSE 0 END).
  • MySQL supports COUNT(DISTINCT a, b) for multiple columns; PostgreSQL needs a row constructor.
Check yourself: A customer placed three orders, each with two line items. After joining orders to order_items, what are COUNT(*) and COUNT(DISTINCT o.id) for that customer?

COUNT(*) is 6 (one per line item) and COUNT(DISTINCT o.id) is 3 (one per order).

Concept check

Quick check: COUNT DISTINCT and FILTER

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Count distinct departments in use

Enterprise Workforce AnalyticseasyPostgreSQL

Return a single value unique_departments โ€” the number of distinct department_id values across all employees.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Shipped vs cancelled order counts

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return two columns in a single query: shipped (orders with status 'shipped') and cancelled (status 'cancelled'). Use FILTER.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Per-department headcount and high earners

Enterprise Workforce AnalyticseasyPostgreSQL

Return department_id, total employees, and high_earners (those with salary > 8000) per department. Use FILTER. Order by department_id.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Final checkpoint

End-of-course high-level quiz

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?

0%

COUNT DISTINCT and FILTER

0/3 solved3 to unlock next
Prevโ˜… Final lesson