6.3 Aggregate Functions (MIN, MAX, SUM, AVG, COUNT)

Level: 6 | Version: v1.1 | Author: Meptrasoft

Overview

An aggregate function performs a calculation on multiple rows and returns a single result.

The five core aggregate functions are:

Aggregate functions summarize data and return a single value.

Practice Tables

This topic reuses the employees and salaries tables from 6.2 Database Objects.

Salaries

MIN() and MAX()

MIN() returns the smallest value, while MAX() returns the largest value.

SELECT
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM salaries;

Result:

MIN() → Smallest value
MAX() → Largest value

SUM()

SUM() calculates the total of the selected numeric values.

SELECT SUM(salary) AS total_salary
FROM salaries;

Result:

SUM() → Total

AVG()

AVG() calculates the average of the selected numeric values.

SELECT AVG(salary) AS average_salary
FROM salaries;

Result:

AVG() → Average

COUNT()

COUNT() counts rows or non-NULL values, depending on how it is used.

Count all rows

SELECT COUNT(*) AS employee_count
FROM employees;

Result:

Count non-NULL salary values

SELECT COUNT(salary) AS salary_count
FROM salaries;

COUNT(salary) counts only rows where salary is not NULL.

COUNT(*) → Counts rows
COUNT(column) → Counts non-NULL values

NULL and Aggregate Functions

A common beginner point is that most aggregate functions ignore NULL values.

For example, consider four salary rows:

Salary:
45000
60000
NULL
50000

SUM(salary) does not treat NULL as zero; the NULL value is ignored in the aggregate calculation.

Similarly:

COUNT(salary) → 3
COUNT(*)      → 4

COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column.

Simple Comparison

Easy Memory Trick

MIN   → LOWEST
MAX   → HIGHEST
SUM   → TOTAL
AVG   → AVERAGE
COUNT → HOW MANY

Common Beginner Mistakes

Placement Quick Points

MIN()   → SMALLEST
MAX()   → LARGEST
SUM()   → TOTAL
AVG()   → AVERAGE
COUNT() → COUNT

• Aggregate functions summarize multiple rows.
• MIN() finds the smallest value.
• MAX() finds the largest value.
• SUM() calculates a total.
• AVG() calculates an average.
• COUNT(*) counts rows.
• COUNT(column) counts non-NULL values.

Interview Questions

What is an aggregate function?

An aggregate function performs a calculation on multiple rows and returns a summarized result.

What are the five common aggregate functions?

TEXT

MIN()
MAX()
SUM()
AVG()
COUNT()

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts rows, while COUNT(column) counts only non-NULL values in that column.

What does AVG() do with NULL values?

AVG() ignores NULL values when calculating the average.

Which function finds the highest salary?

MAX(salary).

Which function calculates total salary?

SUM(salary).

Can aggregate functions be used with GROUP BY?

Yes. GROUP BY allows aggregate calculations to be performed separately for each group.

Example:

SQL

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

Practice & Hands-On Exercises

Using the salaries and employees tables:

1. Find the minimum salary:

SELECT MIN(salary) FROM salaries;

2. Find the maximum salary:

SELECT MAX(salary) FROM salaries;

3. Calculate the total salary:

SELECT SUM(salary) FROM salaries;

4. Calculate the average salary:

SELECT AVG(salary) FROM salaries;

5. Count all employees:

SELECT COUNT(*) FROM employees;

6. Explain the difference between COUNT(*) and COUNT(salary).
7. Add a NULL salary and observe how AVG() behaves.
8. Calculate the average salary for each department using GROUP BY.

💡 Tip: Test your queries using the in-browser interactive runner above.

Key Takeaway

MIN   → LOWEST
MAX   → HIGHEST
SUM   → TOTAL
AVG   → AVERAGE
COUNT → HOW MANY

Aggregate functions summarize data by calculating values such as minimum, maximum, total, average, and count.

End of Aggregate Functions (MIN, MAX, SUM, AVG, COUNT)