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

Show:
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 ✓ Supported 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 2022 FILTER clause added in SQL Server 2022 (compatibility level 130+). Works with standard aggregates.
Oracle ✗ Not Supported — Not supported. Use CASE expressions inside aggregates.
SQLite ✓ Supported 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: Since 9.4 SQL Server: Since 2022 Oracle: Not supported SQLite: Since 3.30.0

Per-Database Syntax & Notes

MySQL

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

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

FILTER clause added in SQL Server 2022 (compatibility level 130+). Works with standard aggregates.

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

Oracle

Not supported. Use CASE expressions inside aggregates.

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

SQLite

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

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