In this article I will explain how to use the Filter Clause in SQL. It is very useful if you have an aggregation function like sum or count and want to give it some conditions.

Prerequisits

In this demonstration I am using a Postgres 15 Database. The Dataset is the Flughafendb. It is originally for MySQL but I converted it to Postgres. If you want to follow along, you can download the Postgres-version here.

Example Task

Imagine you have the task to count how many passengers have the seatnumber ‘7H’. This can be done by a simple WHERE Clause.

SELECT COUNT(*) FROM booking WHERE "seat" = '7H';

 count
--------
 182775
(1 row)
(1.264 seconds)

But if you need the number of passengers with seat ‘7H’ and the number of passengers in seat ‘6G’ it will get more complicated.

Filter Clause

The Filter clause can do exactly that. It filters you aggregation functions like sum, count or avg.

SELECT
	COUNT(*) FILTER (WHERE "seat" = '7H') as "7H",
	COUNT(*) FILTER (WHERE "seat" = '6G') as "6G"
FROM booking;

   7H   |   6G
--------+--------
 182775 | 114352
(1 row)
(1.661 seconds)

SELECT 
	SUM(capacity) FILTER (WHERE "airline_id" = '89') as "spain airlines",
	SUM(capacity) FILTER (WHERE "airline_id" = '23') as "Denmark Airlines"
FROM airplane LIMIT 100;

 spain airlines | Denmark Airlines
----------------+------------------
           4187 |            17699
(1 row)
(0.052 seconds)