COUNT With FILTER Works on PostgreSQL and SQLite and Fails on MySQL and MariaDB

A dashboard counts paid and unpaid orders in one pass with COUNT(*) FILTER (WHERE paid). PostgreSQL and SQLite run it, and MySQL and MariaDB stop at FILTER with a syntax error.

FILTER is standard SQL and limits the rows one aggregate sees, so one query can count several subsets of a table. SQLite reads it from 3.30 and PostgreSQL from 9.4. MySQL and MariaDB refuse it as a syntax error.

What Each Engine Answered

On a table made with

CREATE TABLE t (a INTEGER);
INSERT INTO t VALUES (1), (2), (3);

the example

SELECT COUNT(*) FILTER (WHERE a > 1) FROM t;

gave these answers:

What to Write Instead

This form answers the same on every engine:

SELECT COUNT(CASE WHEN a > 1 THEN 1 END) FROM t;

Move the condition into a CASE inside the aggregate: COUNT(CASE WHEN a > 1 THEN 1 END) counts the same rows, and SUM(CASE WHEN a > 1 THEN a END) adds the same values.

What BareSQL Portable Reports

Saved as example.sql, the example gets this from baresql lint --all:

$ baresql lint --all example.sql
example.sql:3:8: error: COUNT(*) FILTER (WHERE ...) works on SQLite and PostgreSQL; MySQL and MariaDB have no FILTER clause. [aggregate-filter]
    COUNT(*) FILTER (WHERE a > 1)
    Move the condition into a CASE inside the aggregate: COUNT(CASE WHEN a > 1 THEN 1 END) counts the same rows, and SUM(CASE WHEN a > 1 THEN a END) adds the same values.

1 problem: 1 error.

baresql rules aggregate-filter prints the same answers in the terminal, and the BareSQL Portable page lists every difference BareSQL knows.