FILTER clause for conditional aggregation

The FILTER clause attaches a condition directly to a single aggregate (a calculation that collapses many rows into one number), so only the rows that pass that condition feed into that particular total.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Conditional aggregation with FILTER

SELECT COUNT(*) FILTER (WHERE status = 'shipped') AS shipped_count FROM orders

Result:

shipped_count
3

Step by step

Run the query without COUNT so you can see what the unfiltered values look like.
```
SELECT * FILTER (WHERE status='shipped') AS shipped FROM orders;
```

COUNT tells you how many rows match - perfect for sizing a table.

SELECT COUNT(*) FILTER (WHERE status='shipped') AS shipped FROM orders;

AS gives the COUNT result a friendly column name so the output reads cleanly.

SELECT COUNT(*) AS count_result FILTER (WHERE status='shipped') AS shipped FROM orders;

Practice

Try each one in your head first, then open the answer.

1. On orders, count rows where revenue is greater than 100 - use the FILTER clause for conditional aggregation.

SELECT COUNT(*) FILTER (WHERE revenue > 100) FROM orders

2. On store_sales, count rows where revenue is greater than 100 - use the FILTER clause for conditional aggregation.

SELECT COUNT(*) FILTER (WHERE revenue > 100) FROM store_sales

3. On products, count rows where price is greater than 100 - use the FILTER clause for conditional aggregation.

SELECT COUNT(*) FILTER (WHERE price > 100) FROM products

4. On reviews, count rows where rating is greater than 100 - use the FILTER clause for conditional aggregation.

SELECT COUNT(*) FILTER (WHERE rating > 100) FROM reviews

5. On employees, count rows where salary is greater than 100 - use the FILTER clause for conditional aggregation.

SELECT COUNT(*) FILTER (WHERE salary > 100) FROM employees

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed