COUNT DISTINCT and FILTER

Two powerful extensions to standard aggregates for precise counting and conditional aggregation.

COUNT(DISTINCT col) — count unique non-NULL values

-- How many different departments are represented?
SELECT COUNT(DISTINCT department_id) AS unique_departments
FROM employees;

Compare to plain COUNT(department_id) which counts all non-NULL occurrences.

FILTER (WHERE …) — conditional aggregation

FILTER limits an aggregate to rows matching a condition. It is cleaner than CASE WHEN inside the aggregate:

SELECT
  COUNT(*) FILTER (WHERE status = 'shipped')   AS shipped,
  COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;

Works with any aggregate function:

SELECT
  AVG(salary)                               AS overall_avg,
  AVG(salary) FILTER (WHERE department_id = 60) AS it_avg
FROM employees;

Combining GROUP BY with FILTER

SELECT
  department_id,
  COUNT(*)                               AS total,
  COUNT(*) FILTER (WHERE salary > 8000)  AS high_earners
FROM employees
GROUP BY department_id
ORDER BY department_id;

FILTER vs CASE WHEN inside aggregate

-- These are equivalent, but FILTER is more readable:
COUNT(*) FILTER (WHERE salary > 8000)
COUNT(CASE WHEN salary > 8000 THEN 1 END)

Tips

• FILTER is a PostgreSQL (and SQL:2003 standard) feature — not available in older MySQL or SQLite.
• COUNT(DISTINCT col) cannot use the FILTER clause directly in all databases; wrap in a subquery if needed.
• For pivot-style reports, FILTER + GROUP BY replaces multiple sub-selects.