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.

Practice

1. What does the SQL function COUNT(column_name) do in a PostgreSQL query?

easy

A. Finds the average of all values in the specified column.

B. Counts the number of non-null values in the specified column.

C. Calculates the sum of all values in the specified column.

D. Returns the maximum value in the specified column.

Solution

1. Step 1: Understand COUNT function behavior

   The COUNT function counts how many rows have a non-null value in the specified column.

2. Step 2: Differentiate from other aggregate functions

   SUM adds values, AVG finds average, MAX finds maximum, so they do not count rows.

3. Final Answer:

   Counts the number of non-null values in the specified column. -> Option B

4. Quick Check:

   COUNT = number of non-null rows [OK]

Hint: COUNT counts non-null rows, not sums or averages [OK]

Common Mistakes:

• Thinking COUNT sums values
• Assuming COUNT includes nulls
• Confusing COUNT with AVG or MAX

2. Which of the following is the correct syntax to find the average of the price column in a table named products?

easy

A. SELECT AVG price FROM products;

B. SELECT AVG[price] FROM products;

C. SELECT AVG(price) FROM products;

D. SELECT AVERAGE(price) FROM products;

Solution

1. Step 1: Recall correct function syntax

   Aggregate functions like AVG require parentheses around the column name: AVG(column_name).

2. Step 2: Check each option's syntax

   SELECT AVG(price) FROM products; uses correct syntax: AVG(price). Options A, B, and C have syntax errors or invalid function names.

3. Final Answer:

   SELECT AVG(price) FROM products; -> Option C

4. Quick Check:

   AVG(column) uses parentheses [OK]

Hint: Use parentheses around column in aggregate functions [OK]

Common Mistakes:

• Omitting parentheses in AVG
• Using wrong function name like AVERAGE
• Using brackets instead of parentheses

3. Given the table sales with column amount containing values (100, 200, NULL, 300), what is the result of this query?

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

medium

A. (4, 600, 150, 100, 300)

B. (4, 600, 200, 100, 300)

C. (3, 600, 150, 100, 300)

D. (3, 600, 200, 100, 300)

Solution

1. Step 1: Count non-null values in amount

   Values are 100, 200, NULL, 300. COUNT(amount) counts non-null values = 3.

2. Step 2: Calculate SUM, AVG, MIN, MAX ignoring NULL

   SUM = 100+200+300 = 600; AVG = 600/3 = 200; MIN = 100; MAX = 300.

3. Final Answer:

   (3, 600, 200, 100, 300) -> Option D

4. Quick Check:

   COUNT=3, SUM=600, AVG=200 [OK]

Hint: Aggregate functions ignore NULL except COUNT(*) [OK]

Common Mistakes:

• Counting NULL as a value in COUNT(column)
• Dividing AVG by total rows including NULL
• Confusing COUNT(column) with COUNT(*)

4. Identify the error in this query:

SELECT COUNT(*), SUM(price), AVG(price) FROM products WHERE price > 'ten';

medium

A. Comparing price (numeric) with string 'ten' causes error.

B. Using COUNT(*) with WHERE clause is invalid.

C. SUM(price) cannot be used with AVG(price) in same query.

D. Missing GROUP BY clause for aggregate functions.

Solution

1. Step 1: Analyze WHERE clause condition

   The condition price > 'ten' compares a numeric column to a string, which causes a type error in PostgreSQL.

2. Step 2: Check other parts of the query

   COUNT(*) with WHERE is valid; SUM and AVG can be used together; GROUP BY not needed without grouping.

3. Final Answer:

   Comparing price (numeric) with string 'ten' causes error. -> Option A

4. Quick Check:

   Numeric compared to string causes error [OK]

Hint: Compare numeric columns only with numbers, not strings [OK]

Common Mistakes:

• Assuming COUNT(*) cannot be used with WHERE
• Thinking GROUP BY is always required with aggregates
• Mixing data types in WHERE conditions

5. You have a table orders with columns customer_id and total_amount. How do you write a query to find the average order amount for each customer, but only include customers with at least 5 orders?

hard

A. SELECT customer_id, AVG(total_amount) FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5;

B. SELECT customer_id, AVG(total_amount) FROM orders WHERE COUNT(*) >= 5 GROUP BY customer_id;

C. SELECT customer_id, AVG(total_amount) FROM orders GROUP BY customer_id WHERE COUNT(*) >= 5;

D. SELECT customer_id, AVG(total_amount) FROM orders HAVING COUNT(*) >= 5 GROUP BY customer_id;

Solution

1. Step 1: Group orders by customer_id

   Use GROUP BY customer_id to aggregate orders per customer.

2. Step 2: Filter groups with HAVING clause

   HAVING COUNT(*) >= 5 filters customers with at least 5 orders.

3. Step 3: Calculate average order amount per customer

   Use AVG(total_amount) to find average order value per customer.

4. Final Answer:

   SELECT customer_id, AVG(total_amount) FROM orders GROUP BY customer_id HAVING COUNT(*) >= 5; -> Option A

5. Quick Check:

   GROUP BY + HAVING filters groups [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE instead of HAVING for group filters
• Placing HAVING before GROUP BY
• Not grouping by customer_id