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.

Practice

1. Which aggregate function in SQL is used to find the total number of rows in a table?

easy

A. COUNT

B. SUM

C. AVG

D. MAX

Solution

1. Step 1: Understand the purpose of each aggregate function

   COUNT counts the number of rows, SUM adds values, AVG calculates average, and MAX finds the highest value.

2. Step 2: Identify the function that counts rows

   The function that counts rows is COUNT.

3. Final Answer:

   COUNT -> Option A

4. Quick Check:

   Counting rows = COUNT [OK]

Hint: COUNT counts rows, SUM adds numbers [OK]

Common Mistakes:

• Confusing SUM with COUNT
• Using AVG to count rows
• Thinking MAX counts rows

2. Which of the following is the correct syntax to find the average salary from a table named Employees?

easy

A. SELECT SUM(salary) / COUNT(*) FROM Employees;

B. SELECT AVG(salary) FROM Employees;

C. SELECT MEAN(salary) FROM Employees;

D. SELECT AVERAGE(salary) FROM Employees;

Solution

1. Step 1: Recall the standard SQL function for average

   The standard SQL function to calculate average is AVG().

2. Step 2: Check the syntax correctness

   SELECT AVG(salary) FROM Employees; is the correct syntax. Other options use non-standard functions or expressions.

3. Final Answer:

   SELECT AVG(salary) FROM Employees; -> Option B

4. Quick Check:

   Average function = AVG() [OK]

Hint: Use AVG() for average, not AVERAGE or MEAN [OK]

Common Mistakes:

• Using non-standard function names like AVERAGE or MEAN
• Trying to calculate average manually with SUM/COUNT
• Missing parentheses in function call

3. Consider the table Sales with a column amount having values: 100, 200, 300, 400. What will be the result of the query SELECT MAX(amount) FROM Sales;?

medium

A. 400

B. 100

C. 250

D. 1000

Solution

1. Step 1: Identify the values in the column

   The amount column has values 100, 200, 300, and 400.

2. Step 2: Apply the MAX function

   MAX(amount) returns the highest value, which is 400.

3. Final Answer:

   400 -> Option A

4. Quick Check:

   Maximum of 100,200,300,400 = 400 [OK]

Hint: MAX returns highest value in the column [OK]

Common Mistakes:

• Choosing SUM or AVG instead of MAX
• Picking the lowest value by mistake
• Adding values instead of finding max

4. What is wrong with this SQL query?
SELECT SUM(price), COUNT FROM Products;

medium

A. Products table does not exist

B. SUM cannot be used with price column

C. Missing WHERE clause

D. COUNT requires parentheses with a column or *

Solution

1. Step 1: Analyze the use of COUNT function

   The COUNT function must be called with parentheses, like COUNT(*) or COUNT(column_name).

2. Step 2: Identify the syntax error

   The query uses COUNT without parentheses, which is invalid syntax.

3. Final Answer:

   COUNT requires parentheses with a column or * -> Option D

4. Quick Check:

   COUNT() needs parentheses [OK]

Hint: Always use COUNT() with parentheses [OK]

Common Mistakes:

• Writing COUNT without parentheses
• Assuming SUM can't be used on price
• Thinking WHERE clause is mandatory

5. You have a table Orders with columns order_id, customer_id, and amount. You want to find the average order amount for each customer. Which SQL query correctly uses aggregate functions to achieve this?

hard

A. SELECT AVG(amount) FROM Orders WHERE customer_id;

B. SELECT customer_id, SUM(amount) FROM Orders;

C. SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id;

D. SELECT customer_id, COUNT(amount) FROM Orders GROUP BY order_id;

Solution

1. Step 1: Understand the requirement

   We want the average order amount per customer, so we need to group data by customer_id and calculate average amount.

2. Step 2: Check each query's correctness

   SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id; groups by customer_id and uses AVG(amount), which is correct. SELECT customer_id, SUM(amount) FROM Orders; sums amounts but does not group, so it gives total sum only. SELECT AVG(amount) FROM Orders WHERE customer_id; has invalid WHERE clause usage. SELECT customer_id, COUNT(amount) FROM Orders GROUP BY order_id; groups by order_id, which is wrong for per customer aggregation.

3. Final Answer:

   SELECT customer_id, AVG(amount) FROM Orders GROUP BY customer_id; -> Option C

4. Quick Check:

   Group by customer_id + AVG(amount) [OK]

Hint: Use GROUP BY with AVG for per-group averages [OK]

Common Mistakes:

• Not using GROUP BY when aggregating per group
• Using SUM instead of AVG for average
• Incorrect WHERE clause instead of GROUP BY