SQL Functions - MAX, MIN, AVG

1. Overview

Aggregate functions perform calculations on multiple rows to return a single summary value. MAX returns the highest value, MIN the lowest, and AVG the arithmetic mean. They ignore NULL values by default.

2. Basic Usage

2.1 MAX Function

SELECT MAX(salary) AS highest_salary
FROM employees;

Explanation: Retrieves the maximum salary across all employees.

2.2 MIN Function

SELECT MIN(salary) AS lowest_salary
FROM employees;

Explanation: Retrieves the minimum salary across all employees.

2.3 AVG Function

SELECT AVG(salary) AS average_salary
FROM employees;

Explanation: Calculates the average salary across all employees.

3. Grouped Aggregation

Combine these functions with GROUP BY to get summaries per group.

SELECT department,
       MAX(salary) AS max_sal,
       MIN(salary) AS min_sal,
       AVG(salary) AS avg_sal
FROM employees
GROUP BY department;

Result

4. Filtering Groups (HAVING)

SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

Result

5. Intermediate Topics

5.1 Ignoring NULLs

-- salary with some NULLs
SELECT AVG(salary) AS avg_sal
FROM employees;

Explanation: AVG skips NULL salaries unless all are NULL, then returns NULL.

5.2 DISTINCT Modifier

SELECT AVG(DISTINCT salary) AS avg_unique_sal
FROM employees;

Explanation: Averages unique salary values, ignoring duplicates.

6. Advanced Techniques

6.1 Window Functions

SELECT first_name, salary,
  MAX(salary) OVER (PARTITION BY department) AS dept_max,
  MIN(salary) OVER (PARTITION BY department) AS dept_min,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

Explanation: Computes per-department aggregates without collapsing rows.

6.2 CTE-driven Dynamic Thresholds

WITH stats AS (
  SELECT AVG(salary) AS overall_avg_sal
  FROM employees
)
SELECT e.first_name, e.salary
FROM employees e
JOIN stats s ON e.salary > s.overall_avg_sal;

Explanation: Finds employees earning above the company average.

7. Performance Considerations

8. Summary

MAX, MIN, and AVG are essential for summarizing numeric data. Combined with grouping, filtering, and window functions, they provide powerful insights into dataset distributions and trends.