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.