Problem

SQL:2003 introduced filtered aggregations, e.g. SELECT COUNT(*) FILTER(WHERE foo > 0). They're currently supported in Postgres and SQLite.

If I want to use this syntax in Jet, I have to resort to Raw, which is inconvenient. I'd like to be able to build the FILTER(WHERE ...) clause in the same way I build any other WHERE clause.

To be fair, the ANSI SQL form of the FILTER clause is in many cases essentially syntactic sugar, and it can usually be emulated with a CASE statement, but this is not always true. Taking the CASE approach typically relies on aggregate functions ignoring nulls, but this does not work with functions such as Postgres' json_arrayagg, where nulls may or may not be a desired part of the output. Postgres also extends standard SQL by allowing subqueries like EXISTS() in the FILTER clause, and that can't be emulated with a CASE statement either.

Possible solution

Aggregate functions in dialects that support this syntax could return an extended Expression object that would allow adding a FILTER clause, similar to how the window OVER clause is implemented. Rough sketch:

type AggregateIntegerExpression interface {
    IntegerExpression
    FILTER_WHERE(expression BoolExpression) IntegerExpression
}

// usage:
SELECT(
    COUNT(STAR).
        FILTER_WHERE(SomeCol.IS_NOT_NULL))