Aggregate functions (COUNT, SUM, AVG, MAX, MIN) in DBMS Theory - Step-by-Step Execution

Concept Flow - Aggregate functions (COUNT, SUM, AVG, MAX, MIN)

Start with a table of data

↓

Choose aggregate function

↓

Scan all rows

↓

Apply function to column values

↓

Calculate result

↓

Return single summary value

↓

End

Aggregate functions scan a column of data and return one summary value like count, sum, average, max, or min.

Execution Sample

DBMS Theory

SELECT COUNT(age), SUM(salary), AVG(age), MAX(salary), MIN(age) FROM employees;

This query calculates the total number of employees, total salary, average age, highest salary, and youngest age.

Analysis Table

Step	Action	Column Values	Function Applied	Result
1	Read all rows in 'age' column	[25, 30, 22, 40, 28]	COUNT(age)	5
2	Read all rows in 'salary' column	[3000, 4000, 2500, 5000, 3500]	SUM(salary)	18000
3	Read all rows in 'age' column	[25, 30, 22, 40, 28]	AVG(age)	29
4	Read all rows in 'salary' column	[3000, 4000, 2500, 5000, 3500]	MAX(salary)	5000
5	Read all rows in 'age' column	[25, 30, 22, 40, 28]	MIN(age)	22
6	Return all results as one row	-	-	(5, 18000, 29, 5000, 22)

💡 All rows processed, aggregate functions computed, single summary row returned.

State Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
COUNT(age)	0	5	5	5	5	5	5
SUM(salary)	0	0	18000	18000	18000	18000	18000
AVG(age)	0	0	0	29	29	29	29
MAX(salary)	null	null	null	5000	5000	5000	5000
MIN(age)	null	null	null	null	null	22	22

Key Insights - 3 Insights

Why does COUNT(age) return 5 even if some ages might be NULL?

COUNT counts only non-NULL values. In the execution_table row 1, all 5 ages are counted because none are NULL.

How is AVG(age) calculated from the data?

AVG sums all age values and divides by the count of non-NULL ages. From execution_table rows 1 and 3, sum is 145 and count is 5, so average is 29.

Why does SUM(salary) add up to 18000?

SUM adds all salary values from the column. Execution_table row 2 shows the sum of [3000, 4000, 2500, 5000, 3500] equals 18000.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of COUNT(age) after step 1?

A 5

B 0

C 22

D 29

💡 Hint

Check the 'Result' column in row 1 of execution_table.

At which step does the MAX(salary) get its final value?

A Step 2

B Step 5

C Step 4

D Step 6

💡 Hint

Look at the 'Function Applied' column and find where MAX(salary) is computed.

If one salary value was NULL, how would SUM(salary) change in the execution_table?

A SUM would return NULL

B SUM would ignore NULL and sum remaining values

C SUM would count NULL as zero

D SUM would double the total

💡 Hint

Aggregate functions like SUM ignore NULL values, see how COUNT ignores NULL in step 1.

Concept Snapshot

Aggregate functions summarize data columns:
- COUNT counts non-NULL rows
- SUM adds values
- AVG calculates average
- MAX finds highest
- MIN finds lowest
They return one result from many rows.

Full Transcript

Aggregate functions in databases process a column of data to produce a single summary value. For example, COUNT counts how many non-empty entries exist, SUM adds all numbers, AVG finds the average, MAX finds the largest value, and MIN finds the smallest. The execution flow starts by reading all rows, applying the chosen function to the column values, calculating the result, and returning a single summary value. In the example query, COUNT(age) returns 5 because there are five age entries, SUM(salary) adds all salaries to 18000, AVG(age) calculates the average age as 29, MAX(salary) finds the highest salary 5000, and MIN(age) finds the youngest age 22. These functions ignore NULL values in their calculations. This process helps quickly understand large data sets by summarizing key information.