Advanced Aggregate Functions

Beyond the basics (COUNT, SUM, AVG, MIN, MAX — see the Aggregates & GROUP BY lesson), PostgreSQL offers a few extra tools for more expressive summaries.

FILTER — Conditional Aggregation, Cleanly

A cleaner alternative to wrapping every aggregate in CASE WHEN:

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE gender = 'F') AS female_count,
  COUNT(*) FILTER (WHERE salary > 60000) AS high_earners,
  AVG(salary) FILTER (WHERE department = 'Engineering') AS avg_eng_salary
FROM employees;

Compare this to the CASE-based version from the CASE Expressions lesson — FILTER reads more clearly and is the standard modern approach.

STRING_AGG — Concatenate Values Across Rows

Collapses multiple rows into one delimited string — the SQL equivalent of MongoDB's $push followed by joining an array.

SELECT department, STRING_AGG(name, ', ' ORDER BY name) AS employees
FROM employees
GROUP BY department;

 department  |             employees
--------------+-----------------------------------
 Engineering  | Kajal Gupta, Rahul Gupta, Zaid Shaikh

ARRAY_AGG — Collect Values Into an Array

SELECT department, ARRAY_AGG(name ORDER BY salary DESC) AS employees_by_salary
FROM employees
GROUP BY department;

Ordered-Set Aggregates: PERCENTILE_CONT / PERCENTILE_DISC

Compute percentiles/medians directly in SQL, without exporting data to a spreadsheet.

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

SELECT
  department,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees
GROUP BY department;

PERCENTILE_CONT interpolates between values for a smooth result; PERCENTILE_DISC always returns an actual value that exists in the data.

Common Errors & Troubleshooting

• FILTER used on a non-aggregate function — FILTER (WHERE ...) can only follow an aggregate function call (COUNT, SUM, AVG, etc.) — it can't be attached to a plain column reference.
• STRING_AGG producing results in an unpredictable order — always add ORDER BY inside the STRING_AGG(... ORDER BY ...) call itself; without it, the row order (and therefore the concatenated string) isn't guaranteed to be consistent.
• PERCENTILE_CONT erroring outside a WITHIN GROUP clause — ordered-set aggregates like PERCENTILE_CONT/PERCENTILE_DISC require the WITHIN GROUP (ORDER BY ...) syntax specifically — they can't be called like a normal aggregate function.

Key Takeaways

• FILTER (WHERE ...) computes several conditional aggregates in one query, more readably than nesting CASE inside each one.
• STRING_AGG/ARRAY_AGG collapse grouped rows into a single string or array — the direct equivalent of MongoDB's $push.
• PERCENTILE_CONT/PERCENTILE_DISC compute medians and percentiles natively, no external tooling required.