FILTER clause for conditional aggregation in PostgreSQL

Introduction

The FILTER clause helps you count or sum only certain rows that meet a condition, making your results clearer and more useful.

You want to count how many orders were made by each customer, but only those above $100.

You want to find the total sales for each product, but only for sales in the last month.

You want to count how many students passed and how many failed in one query.

You want to calculate average ratings for a product, but only from verified buyers.

Syntax

PostgreSQL

AGGREGATE_FUNCTION(column) FILTER (WHERE condition)

The FILTER clause goes right after the aggregate function.

It lets you apply a condition inside the aggregation without needing separate queries.

Examples

Counts only rows where the status is 'completed'.

PostgreSQL

COUNT(*) FILTER (WHERE status = 'completed')

Sums the amount only for rows in the 'books' category.

PostgreSQL

SUM(amount) FILTER (WHERE category = 'books')

Calculates average score only for rows where passed is true.

PostgreSQL

AVG(score) FILTER (WHERE passed = true)

Sample Program

This query shows total sales, how many were completed, and the sum of amounts for completed sales by product.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product TEXT,
  amount INT,
  status TEXT
);

INSERT INTO sales (product, amount, status) VALUES
('Book', 120, 'completed'),
('Pen', 30, 'pending'),
('Book', 80, 'completed'),
('Pen', 50, 'completed'),
('Book', 200, 'pending');

SELECT
  product,
  COUNT(*) AS total_sales,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed_sales,
  SUM(amount) FILTER (WHERE status = 'completed') AS total_completed_amount
FROM sales
GROUP BY product
ORDER BY product;

Important Notes

You can use FILTER with any aggregate function like COUNT, SUM, AVG, MAX, MIN.

FILTER makes queries easier to read compared to using CASE inside aggregates.

Summary

FILTER lets you apply conditions inside aggregate functions.

It helps count or sum only the rows you want in one query.

It works well with GROUP BY to get detailed summaries.