PostgreSQL SQL Filter Clause

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.


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';

(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.

	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)
	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)
profile picture of the author

Jannik Rehkemper

I'm an professional Linux Administrator and Hobby Programmer. My training as an IT-Professional started in 2019 and ended in 2022. Since 2023 I'm working as an Linux Administrator.