FILTER clause for conditional aggregation in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the FILTER clause do in SQL aggregation?

The FILTER clause lets you apply a condition to an aggregate function, so it only counts or sums rows that meet that condition.

beginner

Write a simple example of using FILTER with COUNT in PostgreSQL.

SELECT COUNT(*) FILTER (WHERE status = 'active') AS active_count FROM users;

intermediate

Why use FILTER instead of CASE WHEN inside aggregate functions?

FILTER is clearer and easier to read because it separates the condition from the aggregate function, making queries simpler and more maintainable.

intermediate

Can you use multiple FILTER clauses in one SELECT statement?

Yes, you can use multiple FILTER clauses with different conditions on different aggregate functions in the same query.

beginner

What will this query return? SELECT SUM(amount) FILTER (WHERE type = 'sale') FROM orders;

It returns the total sum of the 'amount' column but only for rows where the 'type' is 'sale'.

What is the purpose of the FILTER clause in SQL aggregation?

A To apply a condition to aggregate functions

B To filter rows before aggregation

C To sort the results after aggregation

D To join tables conditionally

FILTER applies a condition inside aggregate functions to include only rows that meet the condition.

Which of these is a valid use of FILTER in PostgreSQL?

A COUNT(*) WHERE FILTER age > 30

B COUNT(*) FILTER (WHERE age > 30)

C COUNT(FILTER WHERE age > 30)

D FILTER COUNT(*) WHERE age > 30

The correct syntax is aggregate_function FILTER (WHERE condition).

How does FILTER improve readability compared to CASE WHEN inside aggregates?

A It makes queries longer

B It replaces GROUP BY

C It requires more nested queries

D It separates condition from aggregation clearly

FILTER keeps the condition outside the aggregate function, making the query easier to read.

Can you use FILTER with SUM and COUNT in the same query?

A Yes, with different conditions

B No, only one FILTER per query

C Only with SUM, not COUNT

D Only with COUNT, not SUM

Multiple FILTER clauses can be used with different aggregate functions and conditions.

What does this query do? SELECT COUNT(*) FILTER (WHERE active) FROM users;

A Counts rows where active is false

B Counts all rows ignoring active

C Counts only rows where active is true

D Counts rows with NULL active

FILTER restricts the count to rows where the condition (active) is true.

Explain how the FILTER clause works in SQL aggregation and why it is useful.

Think about how you count or sum only some rows based on a condition.

Write a SQL query using FILTER to count how many orders have status 'completed' and sum amounts for 'pending' orders.

Use FILTER clause separately for COUNT and SUM with different conditions.