Another very interesting feature of Modern SQL is the FILTER clause. It makes it possible to add WHERE clauses to aggregation functions. Let's try the FILTER clause by executing the following query in the SQL tab of the test database in the phpPgAdmin interface:
SELECT SUM(total) as total, SUM(total) FILTER(WHERE closed IS TRUE) as transaction_complete, yearFROM salesGROUP BY year;
You should get the following result:
Result set of the query containing the FILTER statement
The FILTER clause is great for generating reports without adding too much overhead in the query's WHERE clauses.
Moreover, a FILTER clause is great for pivot tables, ...
