COUNT, SUM, AVG, MIN, MAX in PostgreSQL - Step-by-Step Execution

Concept Flow - COUNT, SUM, AVG, MIN, MAX

Start with table data

↓

Choose aggregate function

↓

Scan all rows

↓

Apply function to column values

↓

Return single summary value

↓

End

Aggregate functions scan table rows, apply calculation on column values, and return one summary result.

Execution Sample

PostgreSQL

SELECT COUNT(*) FROM sales;
SELECT SUM(amount) FROM sales;
SELECT AVG(amount) FROM sales;
SELECT MIN(amount) FROM sales;
SELECT MAX(amount) FROM sales;

These queries count rows, sum amounts, find average, minimum, and maximum in the sales table.

Execution Table

Step	Query	Rows Scanned	Operation	Intermediate Result	Final Result
1	SELECT COUNT(*) FROM sales;	5	Count rows	Count=5	5
2	SELECT SUM(amount) FROM sales;	5	Sum amounts	Sum=100+200+150+50+300=800	800
3	SELECT AVG(amount) FROM sales;	5	Sum and count	Sum=800, Count=5	800/5=160
4	SELECT MIN(amount) FROM sales;	5	Find minimum	Min=50	50
5	SELECT MAX(amount) FROM sales;	5	Find maximum	Max=300	300
6	End of queries	N/A	N/A	N/A	N/A

💡 All rows scanned; aggregate functions return single summary values.

Variable Tracker

Variable	Start	After Query 1	After Query 2	After Query 3	After Query 4	After Query 5
Count	0	5	5	5	5	5
Sum	0	0	800	800	800	800
Average	N/A	N/A	N/A	160	160	160
Min	N/A	N/A	N/A	N/A	50	50
Max	N/A	N/A	N/A	N/A	N/A	300

Key Moments - 3 Insights

Why does COUNT(*) count all rows even if some columns have NULL values?

COUNT(*) counts all rows regardless of NULLs because it counts rows, not column values. See execution_table row 1 where all 5 rows are counted.

How does AVG calculate the average if there are NULL values in the column?

AVG ignores NULL values and divides sum by count of non-NULL rows. In our example, all rows have values, so AVG = sum/count (row 3).

Why do MIN and MAX scan all rows instead of stopping early?

MIN and MAX must check every row to find the smallest or largest value. They cannot stop early because a later row might have a smaller or larger value (rows 4 and 5).

Visual Quiz - 3 Questions

Look at the execution_table at step 3, what is the average amount calculated?

A 160

B 800

C 50

D 5

💡 Hint

Check the 'Final Result' column in execution_table row 3.

At which step does the query find the maximum amount?

A Step 2

B Step 5

C Step 1

D Step 4

💡 Hint

Look at the 'Query' column and find MAX in execution_table.

If the sales table had NULL amounts, which aggregate function would still count those rows?

A SUM(amount)

B AVG(amount)

C COUNT(*)

D MIN(amount)

💡 Hint

Refer to key_moments about COUNT(*) counting all rows regardless of NULLs.

Concept Snapshot

COUNT(*) counts all rows including NULLs.
SUM(column) adds all non-NULL values.
AVG(column) calculates average ignoring NULLs.
MIN(column) finds smallest value.
MAX(column) finds largest value.
All return one summary value from many rows.

Full Transcript

Aggregate functions in SQL like COUNT, SUM, AVG, MIN, and MAX process multiple rows to produce a single summary result. COUNT(*) counts all rows regardless of NULLs. SUM adds up all non-NULL values in a column. AVG calculates the average by dividing the sum by the count of non-NULL values. MIN and MAX scan all rows to find the smallest and largest values respectively. Each function scans the entire table and returns one value summarizing the data. This visual trace showed step-by-step how these functions work on a sales table with 5 rows, tracking intermediate and final results.