Combining multiple aggregates in SQL - Step-by-Step Execution

Concept Flow - Combining multiple aggregates

Start Query

↓

Scan Table Rows

↓

Apply Aggregate Functions

↓

SUM()

↓

Combine Results

↓

Return Single Row with All Aggregates

The query scans the table, applies each aggregate function to all rows, then combines these results into one output row.

Execution Sample

SQL

SELECT SUM(sales), COUNT(sales), AVG(sales) FROM orders;

This query calculates the total sales, number of sales, and average sale amount from the orders table.

Execution Table

Step	Action	SUM(sales)	COUNT(sales)	AVG(sales)
1	Start scanning orders table	NULL	NULL	NULL
2	Read row 1: sales=100	100	1	100
3	Read row 2: sales=200	300	2	150
4	Read row 3: sales=50	350	3	116.67
5	Read row 4: sales=150	500	4	125
6	All rows processed	500	4	125
7	Return combined aggregates	500	4	125

💡 All rows processed, aggregates computed and combined into one result row.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
SUM(sales)	0	100	300	350	500	500
COUNT(sales)	0	1	2	3	4	4
AVG(sales)	NULL	100	150	116.67	125	125

Key Moments - 2 Insights

Why does AVG(sales) change after each row instead of only at the end?

AVG is calculated as SUM divided by COUNT at each step, so it updates as new rows are processed (see execution_table rows 2-5).

Why do all aggregates return a single row instead of multiple rows?

Aggregate functions summarize all rows into one result, so the query returns one combined row with all aggregates (see execution_table row 7).

Visual Quiz - 3 Questions

Look at the execution_table at step 4, what is the COUNT(sales)?

A 2

B 4

C 3

D 1

💡 Hint

Check the COUNT(sales) column in execution_table row with Step 4.

At which step does SUM(sales) reach 500?

A Step 3

B Step 5

C Step 6

D Step 2

💡 Hint

Look at the SUM(sales) column and find when it first equals 500.

If a new row with sales=100 is added, what will COUNT(sales) be at final?

A 5

B 4

C 6

D 3

💡 Hint

COUNT increases by 1 for each new row processed (see variable_tracker COUNT row).

Concept Snapshot

Combining multiple aggregates:
Use multiple aggregate functions (SUM, COUNT, AVG) in one SELECT.
Each processes all rows and returns one combined result row.
AVG is SUM divided by COUNT, updated as rows are read.
Result is a single row with all aggregates together.

Full Transcript

This visual execution shows how SQL combines multiple aggregate functions in one query. The query scans each row of the orders table, updating SUM, COUNT, and AVG step by step. SUM adds sales amounts, COUNT counts rows, and AVG calculates the average as SUM divided by COUNT. After all rows are processed, the query returns one row with all three aggregate results combined. This helps beginners see how aggregates work together and produce a single summary output.