The FILTER Clause

The FILTER Clause

The FILTER Clause for Conditional Aggregates

MySQL 524 pivots with SUM(CASE WHEN channel = 'ios' THEN amount END). The standard FILTER (WHERE ...) clause, supported by PostgreSQL 1,289 and DuckDB 61,228 but not MySQL, says it directly and works with any aggregate:

Sales per channel as columns, and the return rate, per genreSQL

SELECT genre,
       sum(gross_amount) FILTER (WHERE channel = 'ios')     AS ios,
       sum(gross_amount) FILTER (WHERE channel = 'android') AS android,
       sum(gross_amount) FILTER (WHERE channel = 'web')     AS web,
       round(100.0 * count(*) FILTER (WHERE status = 'returned') / count(*), 2) AS returned_pct
FROM mart.sales
WHERE status <> 'cancelled'
GROUP BY genre
ORDER BY genre;

Output

      genre      |    ios    |  android  |    web    | returned_pct
-----------------+-----------+-----------+-----------+--------------
 Cooking         | 321984.00 | 250056.00 | 144912.00 |         4.20
 Fiction         | 307055.16 | 243032.87 | 143274.42 |         4.21
 ...
 Technology      | 420003.50 | 325717.00 | 188810.00 |         4.26
 Travel          |  41643.75 |  30937.50 |  17550.00 |         3.39

It is more than style: the CASE form of a conditional count silently miscounts if someone writes ELSE 0, and FILTER also works with array_agg or percentile_cont. For pivots over values not known in advance, DuckDB's PIVOT statement or PostgreSQL's tablefunc extension (crosstab()) generate the columns.

MENU