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,
   year
FROM sales
GROUP BY year;

You should get the following result:

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, where grouping by year and month is made more complicated by the fact that it is necessary to generate a report that must cross month and year on two different axes (month = x and year = y, for example).

Let's continue with the last Modern SQL feature, LATERAL queries.