FILTER clause for conditional aggregation in PostgreSQL - Step-by-Step Execution

Concept Flow - FILTER clause for conditional aggregation

Start Query

↓

GROUP BY rows

↓

Apply aggregate functions

↓

Check FILTER condition for each aggregate

↓

Include row

↓

Calculate aggregate result

↓

Return grouped results

The query groups rows, then for each aggregate function, it includes only rows that meet the FILTER condition before calculating the aggregate.

Execution Sample

PostgreSQL

SELECT department,
       COUNT(*) FILTER (WHERE salary > 50000) AS high_earners,
       COUNT(*) AS total_employees
FROM employees
GROUP BY department;

Counts employees per department, counting only those with salary > 50000 as high earners.

Execution Table

Step	Row Data (department, salary)	Aggregate	FILTER Condition Met?	Action	Aggregate State
1	(Sales, 60000)	COUNT(*) FILTER (WHERE salary > 50000)	Yes	Include in high_earners count	high_earners=1
1	(Sales, 60000)	COUNT(*)	N/A	Include in total_employees count	total_employees=1
2	(Sales, 45000)	COUNT(*) FILTER (WHERE salary > 50000)	No	Exclude from high_earners count	high_earners=1
2	(Sales, 45000)	COUNT(*)	N/A	Include in total_employees count	total_employees=2
3	(HR, 52000)	COUNT(*) FILTER (WHERE salary > 50000)	Yes	Include in high_earners count	high_earners=1
3	(HR, 52000)	COUNT(*)	N/A	Include in total_employees count	total_employees=1
4	(HR, 48000)	COUNT(*) FILTER (WHERE salary > 50000)	No	Exclude from high_earners count	high_earners=1
4	(HR, 48000)	COUNT(*)	N/A	Include in total_employees count	total_employees=2
5	(IT, 70000)	COUNT(*) FILTER (WHERE salary > 50000)	Yes	Include in high_earners count	high_earners=1
5	(IT, 70000)	COUNT(*)	N/A	Include in total_employees count	total_employees=1
6	(IT, 30000)	COUNT(*) FILTER (WHERE salary > 50000)	No	Exclude from high_earners count	high_earners=1
6	(IT, 30000)	COUNT(*)	N/A	Include in total_employees count	total_employees=2
7	Group Sales complete	Final counts	N/A	Output: high_earners=1, total_employees=2
8	Group HR complete	Final counts	N/A	Output: high_earners=1, total_employees=2
9	Group IT complete	Final counts	N/A	Output: high_earners=1, total_employees=2
10	Query complete	All groups processed	N/A	Return final result set

💡 All rows processed and aggregates computed per group.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	Final
high_earners (Sales)	0	1	1					1
total_employees (Sales)	0	1	2					2
high_earners (HR)	0			1	1			1
total_employees (HR)	0			1	2			2
high_earners (IT)	0					1	1	1
total_employees (IT)	0					1	2	2

Key Moments - 3 Insights

Why does the FILTER clause exclude some rows from the aggregate?

Because FILTER applies a condition to decide if a row counts for that aggregate. Rows not meeting the condition are ignored for that aggregate but still counted in others, as shown in execution_table rows 2 and 4.

Does FILTER affect the GROUP BY operation?

No, FILTER only affects which rows are counted inside an aggregate function after grouping. Grouping happens first, then FILTER decides which rows inside each group to include.

Can FILTER be used with any aggregate function?

Yes, FILTER works with aggregates like COUNT, SUM, AVG, etc., to conditionally include rows, as demonstrated with COUNT in the example.

Visual Quiz - 3 Questions

Look at the execution_table at Step 2 for the Sales row with salary 45000. Does this row count towards high_earners?

A No, it is excluded

B Yes, it is included

C Only partially included

D It depends on other rows

💡 Hint

Check the 'FILTER Condition Met?' column at Step 2 for that row.

At which step does the total_employees count for Sales reach 2?

A After Step 1

B After Step 2

C After Step 3

D After Step 4

💡 Hint

Look at the 'Aggregate State' for total_employees in Sales rows.

If the FILTER condition changed to salary > 60000, how would the high_earners count for IT change?

A It would decrease to 0

B It would increase

C It would stay the same

D It would count all employees

💡 Hint

Refer to variable_tracker for high_earners (IT) and consider which salaries meet the new condition.

Concept Snapshot

FILTER clause syntax:
AGG_FUNC(...) FILTER (WHERE condition)

Behavior:
- Applies condition inside aggregate
- Only rows meeting condition count
- Other rows ignored for that aggregate

Key rule:
FILTER does not affect grouping, only aggregation.

Full Transcript

This visual execution shows how the FILTER clause works in PostgreSQL aggregation. The query groups employees by department. For each group, it counts total employees and counts only those with salary over 50000 as high earners. Each row is checked against the FILTER condition before counting in the filtered aggregate. Rows not meeting the condition are excluded from that aggregate but still counted in others. The execution table traces each row's effect on aggregates step-by-step. The variable tracker shows how counts update per group. Key moments clarify common confusions about FILTER's role and scope. The quiz tests understanding by referencing specific steps and variable states.

Practice

1. What does the FILTER clause do in a PostgreSQL aggregate function?

easy

A. It filters the entire table before aggregation starts.

B. It applies a condition to include only certain rows in the aggregation.

C. It changes the grouping columns in the query.

D. It sorts the results after aggregation.

Solution

1. Step 1: Understand the purpose of FILTER in aggregation

   The FILTER clause allows you to specify a condition inside an aggregate function to include only rows that meet that condition.

2. Step 2: Compare with other options

   FILTER does not filter the whole table before aggregation, nor does it change grouping or sort results. It only limits rows counted or summed inside the aggregate.

3. Final Answer:

   It applies a condition to include only certain rows in the aggregation. -> Option B

4. Quick Check:

   FILTER = condition inside aggregate [OK]

Hint: FILTER applies conditions inside aggregates, not on whole table [OK]

Common Mistakes:

• Thinking FILTER filters rows before aggregation
• Confusing FILTER with WHERE clause
• Assuming FILTER changes GROUP BY columns

2. Which of the following is the correct syntax to count only rows where status = 'active' using FILTER in PostgreSQL?

easy

A. SELECT COUNT(*) FILTER (WHERE status = 'active') FROM users;

B. SELECT COUNT(*) FROM users FILTER (WHERE status = 'active');

C. SELECT COUNT(*) WHERE FILTER status = 'active' FROM users;

D. SELECT COUNT(*) FILTER WHERE status = 'active' FROM users;

Solution

1. Step 1: Recall FILTER syntax in aggregate functions

   The correct syntax places FILTER with parentheses and a WHERE clause inside the aggregate function: COUNT(*) FILTER (WHERE condition).

2. Step 2: Check each option

   SELECT COUNT(*) FILTER (WHERE status = 'active') FROM users; matches the correct syntax. The other options misuse FILTER placement or syntax.

3. Final Answer:

   SELECT COUNT(*) FILTER (WHERE status = 'active') FROM users; -> Option A

4. Quick Check:

   FILTER syntax = FILTER (WHERE condition) [OK]

Hint: Use FILTER (WHERE condition) inside aggregate parentheses [OK]

Common Mistakes:

• Omitting parentheses after FILTER
• Placing FILTER outside aggregate function
• Using FILTER without WHERE keyword

3. Given the table sales with columns region and amount, what will this query return?

SELECT region, SUM(amount) FILTER (WHERE amount > 100) AS big_sales FROM sales GROUP BY region;

medium

A. Sum of all sales amounts per region.

B. Count of sales with amount greater than 100 per region.

C. Sum of sales amounts less than or equal to 100 per region.

D. Sum of sales amounts greater than 100 per region.

Solution

1. Step 1: Understand the FILTER condition in SUM

   The FILTER clause limits the SUM to only rows where amount > 100.

2. Step 2: Analyze the GROUP BY effect

   The query groups rows by region, then sums only amounts above 100 in each group.

3. Final Answer:

   Sum of sales amounts greater than 100 per region. -> Option D

4. Quick Check:

   SUM with FILTER (WHERE amount > 100) = sum of big sales [OK]

Hint: FILTER limits rows inside SUM to amount > 100 [OK]

Common Mistakes:

• Thinking it sums all amounts, ignoring FILTER
• Confusing SUM with COUNT
• Ignoring GROUP BY effect

4. Identify the error in this query:

SELECT department, COUNT(*) FILTER (status = 'active') FROM employees GROUP BY department;

medium

A. Missing WHERE keyword inside FILTER clause.

B. GROUP BY should include status column.

C. COUNT(*) cannot be used with FILTER.

D. FILTER clause must be outside aggregate function.

Solution

1. Step 1: Check FILTER clause syntax

   FILTER requires a WHERE keyword before the condition: FILTER (WHERE condition).

2. Step 2: Verify other parts

   GROUP BY on department is correct. COUNT(*) works with FILTER. FILTER must be inside aggregate function.

3. Final Answer:

   Missing WHERE keyword inside FILTER clause. -> Option A

4. Quick Check:

   FILTER syntax needs WHERE [OK]

Hint: Always include WHERE inside FILTER parentheses [OK]

Common Mistakes:

• Omitting WHERE in FILTER
• Placing FILTER outside aggregate
• Adding unnecessary columns to GROUP BY

5. You want to find the total sales and the total sales above 500 for each store in one query. Which query correctly uses FILTER to achieve this?

hard

A. SELECT store, SUM(amount) FILTER (WHERE amount > 500) AS total_sales, SUM(amount) AS big_sales FROM sales GROUP BY store;

B. SELECT store, SUM(amount) AS total_sales, COUNT(amount) FILTER (WHERE amount > 500) AS big_sales FROM sales GROUP BY store;

C. SELECT store, SUM(amount) AS total_sales, SUM(amount) FILTER (WHERE amount > 500) AS big_sales FROM sales GROUP BY store;

D. SELECT store, COUNT(amount) AS total_sales, SUM(amount) FILTER (WHERE amount > 500) AS big_sales FROM sales GROUP BY store;

Solution

1. Step 1: Identify total sales calculation

   Total sales is the sum of all amounts per store, so SUM(amount) without filter is correct.

2. Step 2: Identify big sales calculation

   Big sales are sums of amounts greater than 500, so SUM(amount) FILTER (WHERE amount > 500) is correct.

3. Step 3: Check options

   SELECT store, SUM(amount) AS total_sales, SUM(amount) FILTER (WHERE amount > 500) AS big_sales FROM sales GROUP BY store; correctly assigns total_sales and big_sales. The other options mix sums and counts incorrectly or swap columns.

4. Final Answer:

   SELECT store, SUM(amount) AS total_sales, SUM(amount) FILTER (WHERE amount > 500) AS big_sales FROM sales GROUP BY store; -> Option C

5. Quick Check:

   SUM with and without FILTER for totals [OK]

Hint: Use SUM(amount) and SUM(amount) FILTER (WHERE ...) together [OK]

Common Mistakes:

• Swapping filtered and unfiltered sums
• Using COUNT instead of SUM for amounts
• Misplacing FILTER clause