FILTER Clause (Aggregate)

FILTER Clause (Aggregate) is a SQL function in the Aggregate Functions category. PostgreSQL/SQLite: conditionally includes rows in an aggregate without affecting the GROUP BY. The syntax is AGG_FUNC(column) FILTER (WHERE condition). It accepts column. It returns filtered aggregate value. A typical example: SELECT department, COUNT(*) AS total_employees, COUNT(*) FILTER (WHERE salary > 80000) AS high_earners, AVG(salary) FILTER (WHERE salary > 50000) AS avg_above_50k FROM employees GROUP BY department; -- Multiple filtered aggregates, clean and efficient A close relative is COUNT(), which returns the number of rows (or non-NULL values) in a group. A close relative is SUM(), which returns the sum of all non-NULL values in a group. Works with numeric columns. A close relative is AVG(), which returns the average (mean) of all non-NULL values in a group. More about this category: Aggregation functions — COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING. Related Aggregate Functions entries: COUNT(), SUM(), AVG(), MIN(), MAX(), GROUP_CONCAT() / LISTAGG(), STRING_AGG(), VARIANCE() / VAR_SAMP().