Conditional aggregation pattern in SQL - Step-by-Step Execution

Concept Flow - Conditional aggregation pattern

Start with data table

↓

Apply condition inside aggregation

↓

Aggregate values meeting condition

↓

Return aggregated result

↓

End

The flow shows how SQL applies a condition inside an aggregate function to summarize data selectively.

Execution Sample

SQL

SELECT
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  SUM(CASE WHEN amount > 100 THEN amount ELSE 0 END) AS big_sales
FROM sales;

This query counts active statuses and sums sales amounts over 100 from the sales table.

Execution Table

Step	Row Data	Condition (status='active')	Count Action	Condition (amount>100)	Sum Action	Running Count	Running Sum
1	{status: 'active', amount: 150}	True	Count +1	True	Sum +150	1	150
2	{status: 'inactive', amount: 90}	False	Count +0	False	Sum +0	1	150
3	{status: 'active', amount: 200}	True	Count +1	True	Sum +200	2	350
4	{status: 'active', amount: 50}	True	Count +1	False	Sum +0	3	350
5	{status: 'inactive', amount: 300}	False	Count +0	True	Sum +300	3	650
6	{status: 'active', amount: 120}	True	Count +1	True	Sum +120	4	770
Exit	No more rows	-	-	-	-	Final count=4	Final sum=770

💡 All rows processed; aggregation complete.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	Final
active_count	0	1	1	2	3	3	4	4
big_sales	0	150	150	350	350	650	770	770

Key Moments - 2 Insights

Why does the count only increase when status is 'active'?

Because the COUNT uses CASE to include only rows where status='active' (see execution_table rows 1,3,4,6). Rows with other statuses add zero to count.

Why does the sum only add amounts greater than 100?

The SUM uses CASE to add amount only if amount>100, else adds 0 (see execution_table rows 1,3,5,6). Amounts 90 and 50 do not increase sum.

Visual Quiz - 3 Questions

Look at the execution_table, what is the active_count after processing row 4?

A 3

B 2

C 4

D 1

💡 Hint

Check the 'Running Count' column at step 4 in execution_table.

At which step does the big_sales sum first exceed 300?

A Step 4

B Step 3

C Step 5

D Step 6

💡 Hint

Look at the 'Running Sum' column in execution_table to find when sum passes 300.

If the condition for counting changed to status='inactive', what would active_count be after step 6?

A 3

B 4

C 2

D 1

💡 Hint

Count how many rows have status='inactive' in the Row Data column.

Concept Snapshot

Conditional aggregation uses CASE inside aggregate functions.
Example: COUNT(CASE WHEN condition THEN 1 END) counts rows meeting condition.
SUM(CASE WHEN condition THEN value ELSE 0 END) sums values meeting condition.
This lets you summarize data selectively in one query.
Useful for counting or summing based on filters without WHERE.

Full Transcript

This visual execution shows how SQL performs conditional aggregation by applying conditions inside aggregate functions like COUNT and SUM. Each row is checked against conditions: if status equals 'active', the count increases by one; if amount is greater than 100, the sum adds that amount. The execution table tracks each row's data, condition results, and running totals. The variable tracker shows how active_count and big_sales change after each row. Key moments clarify why only certain rows affect counts or sums. The quiz tests understanding of running totals and condition effects. The snapshot summarizes the pattern: use CASE inside aggregates to count or sum conditionally in one query.