---
title: "FILTER in PostgreSQL (With Examples)"
description: "Learn how to use the FILTER clause in PostgreSQL to perform multiple query aggregations. A practical guide for PostgreSQL and TimescaleDB developers."
section: "Postgres basics"
---

> **TimescaleDB is now Tiger Data.**

*Written by Team Timescale*

In PostgreSQL and TimescaleDB, the [`FILTER`](https://www.postgresql.org/docs/14/sql-expressions.html) clause can be used to extend aggregate functions like [sum(), avg(), and count()](https://www.timescale.com/learn/postgresql-aggregate-functions) by adding a `WHERE` clause. This is especially useful when you want to perform multiple aggregations in your query.

When `FILTER` is used with an aggregate function, only the input rows that its `WHERE` clause evaluates to be true will be used in the aggregate clause. The data being processed by the aggregate function is “filtered” by the `WHERE` clause’s condition.

Syntax:

`<aggregate_function>(<expression>) FILTER(WHERE <condition>)
`

If you use an aggregate function with a [window function](https://www.timescale.com/learn/postgresql-window-functions) call, here is the syntax:

`<aggregate_function>(<expression>) FILTER(WHERE <condition>) OVER(<window_definition)
`

A `FILTER` clause can only be used with window functions that are aggregates.



## **Examples**

Let’s look at some examples to better understand `FILTER` clauses. For the first set of examples, we’ll use the data in this table called `car_sales`.

| sales_year | sales_month | make | model | kind | quantity | revenue |
| --- | --- | --- | --- | --- | --- | --- |
| 2021 | 1 | Ford | F100 | PickUp | 40 | 2500000 |
| 2021 | 1 | Ford | Mustang | Car | 9 | 1010000 |
| 2021 | 1 | Renault | Fuego | Car | 20 | 9000000 |
| 2021 | 2 | Renault | Fuego | Car | 50 | 23000000 |
| 2021 | 2 | Ford | F100 | PickUp | 20 | 1200000 |
| 2021 | 2 | Ford | Mustang | Car | 10 | 1050000 |
| 2021 | 3 | Renault | Megane | Car | 50 | 20000000 |
| 2021 | 3 | Renault | Koleos | Car | 15 | 1004000 |
| 2021 | 3 | Ford | Mustang | Car | 20 | 2080000 |
| 2021 | 4 | Renault | Megane | Car | 50 | 20000000 |
| 2021 | 4 | Renault | Koleos | Car | 15 | 1004000 |
| 2021 | 4 | Ford | Mustang | Car | 25 | 2520000 |



### **Replacing a CASE Statement With FILTER**

PostgreSQL didn’t have `FILTER` until version 9.4. Before this version, database developers often used a `CASE` statement and `WHEN` clauses to get the results you can now get with `FILTER`. This method was much less straightforward.

Let’s start with a simple query to get the minimum and maximum revenue per car maker. Here is the query to get the results using the `FILTER` clause:

`SELECT
	min(revenue) FILTER (WHERE make = ‘Ford’) min_ford,
	max(revenue) FILTER (WHERE make = ‘Ford’) max_ford,
	min(revenue) FILTER (WHERE make = ‘Renault’) min_renault,
	max(revenue) FILTER (WHERE make = ‘Renault’) max_renault
FROM
	car_sales;`

We created an alias for each of these sums with the `AS` keyword so that we can differentiate the results, or else each column in the result set would be labeled simply `min` or `max`. Here are the results:

| min_ford | max_ford | min_renault | max_renault |
| --- | --- | --- | --- |
| 1010000 | 2520000 | 1004000 | 23000000 |

To get the same results with a CASE statement, you would have to use this query:

`SELECT
	min(CASE WHEN make = ‘Ford’ THEN revenue ELSE null END) min_ford,
	max(CASE WHEN make = ‘Ford’ THEN revenue ELSE null END) max_ford,
	min(CASE WHEN make = ‘Renault’ THEN revenue ELSE null END) min_renault,
	max(CASE WHEN make = ‘Renault’ THEN revenue ELSE null END) max_renault
FROM
	car_sales;`

For cases like this, `FILTER` is easier to understand.



### **Pivoting Tables With FILTER**

We can use the `FILTER` clause to pivot rows into tables, which you often need to do to generate reports. Let’s say we want to know the total quantity of cars sold in each month. We could use [`GROUP BY`](https://www.timescale.com/learn/understanding-group-by-in-postgresql-with-examples) to do that with a query like this:

`SELECT sales_month, sum(quantity) FROM car_sales GROUP BY sales_month;`

These are the results:

| sales_month | sum |
| --- | --- |
| 4 | 90 |
| 3 | 85 |
| 1 | 69 |
| 2 | 80 |

Or we could use `FILTER` to pivot these results with a query like this:

`SELECT
	sum(quantity) FILTER (WHERE sales_month = 1) jan_quantity,
	sum(quantity) FILTER (WHERE sales_month = 2) feb_quantity,
	sum(quantity) FILTER (WHERE sales_month = 3) mar_quantity,
	sum(quantity) FILTER (WHERE sales_month = 4) apr_quantity
FROM
	car_sales;`

Results:

| jan_quantity | feb_quantity | mar_quantity | apr_quantity |
| --- | --- | --- | --- |
| 69 | 80 | 85 | 90 |



### **Using FILTER Clauses With Window Functions**

For the next example, we are going to use a different data set that contains temperature and precipitation data from two cities.

| day | city | temperature | precipitation |
| --- | --- | --- | --- |
| 2021-09-04 | Miami | 68.36 | 0.00 |
| 2021-09-05 | Miami | 72.50 | 0.00 |
| 2021-09-01 | Miami | 65.30 | 0.28 |
| 2021-09-02 | Miami | 64.40 | 0.79 |
| 2021-09-03 | Miami | 71.60 | 0.47 |
| 2021-09-04 | Atlanta | 67.28 | 0.00 |
| 2021-09-05 | Atlanta | 70.80 | 0.00 |
| 2021-09-01 | Atlanta | 63.14 | 0.20 |
| 2021-09-02 | Atlanta | 62.60 | 0.59 |
| 2021-09-03 | Atlanta | 62.60 | 0.39 |

In the next query, we are going to get the three-day moving average of the temperature in each city. To do this, we will define a window using the `OVER` clause and partition it by the city. To show how `FILTER` works with the window clause, we will only return the maximum temperature when the temperature for any day in a window, including the current row plus the two rows before is over 70 degrees. In other words, it will give us the highest temperature of the last three days, inclusive of any time the temperature for the day is over 70.

Here is the query:

`SELECT city, day, temperature,
    MAX(temperature)
    FILTER (WHERE temperature > 70)
    OVER (
      PARTITION BY city
      ORDER BY day ASC
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) max_3day
FROM city_data
ORDER BY city, day;`

And here are the results:

| city | day | temperature | max_3day |
| --- | --- | --- | --- |
| Atlanta | 2021-09-01 | 63.14 |
| Atlanta | 2021-09-02 | 62.60 |
| Atlanta | 2021-09-03 | 62.60 |
| Atlanta | 2021-09-04 | 67.28 |
| Atlanta | 2021-09-05 | 70.80 | 70.80 |
| Miami | 2021-09-01 | 65.30 |
| Miami | 2021-09-02 | 64.40 |
| Miami | 2021-09-03 | 71.60 | 71.60 |
| Miami | 2021-09-04 | 68.36 | 71.60 |
| Miami | 2021-09-05 | 72.50 | 72.50 |



## **Next Steps**

To learn more about the `FILTER` clause and how to use it in PostgreSQL, you can check out PostgreSQL’s documentation on [aggregate expressions](https://www.postgresql.org/docs/14/sql-expressions.html#SYNTAX-AGGREGATES) and [window function calls](https://www.postgresql.org/docs/14/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS).

If you want to experiment with `FILTER` in a PostgreSQL (but faster) database while enjoying the benefits of [automatic data partitioning](https://www.timescale.com/learn/postgresql-partition-strategies-and-more) and [columnar compression](https://www.tigerdata.com/blog/building-columnar-compression-in-a-row-oriented-database) to speed up performance and reduce storage, [create a free Timescale account today](https://console.cloud.timescale.com/signup).