FILTER (AGGREGATE)

Functions
Buy Me A Coffee

Add a per-aggregate WHERE condition to filter the rows included in that specific aggregate, without a separate subquery.

PostgreSQL FILTER clause on aggregate functions computes the aggregate only for rows matching a WHERE-like condition, without needing a GROUP BY. More readable than wrapping aggregates in CASE, especially when filtering is inherently about the aggregate rather than the row.

Compatibility

Filter by Database
SQL FILTER (AGGREGATE) Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Not Supported - Not supported. Use SUM(CASE WHEN cond THEN 1 ELSE 0 END) or COUNT(CASE WHEN cond THEN 1 END) as a workaround.
PostgreSQL Native 9.4 Full support on all aggregate functions including COUNT, SUM, AVG, array_agg, etc. Also works on window functions when the window function is itself an aggregate.
SQL Server Supported via workaround - Workaround available in all listed versions. No native aggregate FILTER (WHERE ...) clause. Use CASE expressions inside aggregates.
Oracle Not Supported - Not supported. Use CASE expressions inside aggregates.
SQLite Native 3.30.0 FILTER clause added in SQLite 3.30.0 (October 2019). Works with all built-in aggregates.

Details

Defined in the SQL standard (ISO/IEC 9075:2003). Oracle is the notable holdout. The CASE workaround is portable across all engines but significantly more verbose.

Standard Syntax

SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_count, COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count, AVG(salary) FILTER (WHERE dept = 'eng') AS eng_avg_salary FROM employees;

Version Support

MySQL: Not supported PostgreSQL: Native since 9.4 SQL Server: Supported via workaround in all listed versions Oracle: Not supported SQLite: Native since 3.30.0

Per-Database Syntax & Notes

MySQL Alternative syntax

Not supported. Use SUM(CASE WHEN cond THEN 1 ELSE 0 END) or COUNT(CASE WHEN cond THEN 1 END) as a workaround.

SELECT SUM(CASE WHEN status='active' THEN 1 ELSE 0 END) AS active_count, SUM(CASE WHEN status='inactive' THEN 1 ELSE 0 END) AS inactive_count FROM employees;

PostgreSQL Native syntax

Full support on all aggregate functions including COUNT, SUM, AVG, array_agg, etc. Also works on window functions when the window function is itself an aggregate.

SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_count, AVG(salary) FILTER (WHERE dept = 'eng') AS eng_avg FROM employees;

SQL Server Workaround

Use CASE expressions inside aggregate functions to conditionally include rows. Available in all listed versions. No native aggregate FILTER (WHERE ...) clause. Use CASE expressions inside aggregates.

SELECT SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count, AVG(CASE WHEN dept = 'eng' THEN salary END) AS eng_avg_salary FROM employees;

Oracle Alternative syntax

Not supported. Use CASE expressions inside aggregates.

SELECT COUNT(CASE WHEN status='active' THEN 1 END) AS active_count FROM employees;

SQLite Native syntax

FILTER clause added in SQLite 3.30.0 (October 2019). Works with all built-in aggregates.

SELECT COUNT(*) FILTER (WHERE status = 'active') FROM employees;