Aggregations are a fundamental feature of SQL. Functions such as min, max, count, sum, avg and so on are used in virtually every application – it is basically impossible to write a reasonably complex applications without them.
Here is a trivial aggregate: All it does is summing up numbers from 1 to 10:
test=# SELECT sum(x) FROM generate_series(1, 10) AS x; sum ----- 55 (1 row)
So far so good. But what if we only want to sum up even numbers? We can rely on a simple trick: NULL values are ignored by aggregation functions. This makes a lot of sense because NULL really means “undefined” and you cannot average or sum up undefined values. A simple CASE WHEN can do the job:
test=# SELECT sum(CASE WHEN x % 2 = 0 THEN x ELSE NULL END) FROM generate_series(1, 10) AS x; sum ----- 30 (1 row)
This works perfectly – however, it is a bit old fashioned and pretty nasty to write (especially in case of more complex operations).
The fancy way …
There is a more fancy way to do that:
test=# SELECT sum(x) FILTER (WHERE x % 2 = 0) AS even, sum(x) FILTER (WHERE x % 2 = 1) AS odd FROM generate_series(1, 10) AS x; even | odd ------+----- 30 | 25 (1 row)
The FILTER keywords allow user to tell the systems, which rows should make it into the aggregate function. The new syntax is ways more readable and basically also a lot shorter.