SQL Aggregate Functions
In this chapter you will learn following SQL Aggregate Functions:
- SQL Min()
- SQL Max()
- SQL Count()
- SQL Sum()
- SQL Avg()
SQL aggregate functions perform a calculation on a set of values and return a single, summarized value. They are most commonly used in combination with the GROUP BY clause to break down summary statistics across specific data segments.
Except for COUNT(*), all SQL aggregate functions ignore NULL values by default when performing calculations.
Quick Comparison Table
| Function | Description | Supported Data Types | Example Query |
|---|---|---|---|
| MIN() | Returns the smallest value. | Numbers, Strings, Dates | SELECT MIN(price) FROM products; |
| MAX() | Returns the largest value. | Numbers, Strings, Dates | SELECT MAX(price) FROM products; |
| COUNT() | Returns the number of items/rows. | Any data type | SELECT COUNT(*) FROM orders; |
| SUM() | Calculates the total sum. | Numeric types only | SELECT SUM(salary) FROM employees; |
| AVG() | Calculates the mathematical mean. | Numeric types only | SELECT AVG(salary) FROM employees; |
Detailed Breakdown
MIN() and MAX()
These functions look through an entire column to return the absolute lowest or highest value.
- Dates: MIN() gives the earliest date; MAX() gives the latest date.
- Strings: They evaluate data alphabetically (MIN() finds words starting with "A", MAX() finds words starting with "Z").
-- Find the lowest and highest price in the inventory
SELECT MIN(price) AS lowest_price, MAX(price) AS highest_price FROM products;
COUNT()
The behaviour of COUNT() changes entirely depending on what you pass inside the parentheses:
- COUNT(*): Counts every single row in the dataset, including rows that contain NULL values or duplicates.
- COUNT(column_name): Counts only the rows where that specific column contains a non-NULL value.
- COUNT(DISTINCT column_name): Counts only the unique, non-NULL entries.
-- Count total customers and unique countries represented
SELECT COUNT(*) AS total_rows, COUNT(DISTINCT country) AS unique_countries FROM customers;
SUM() and AVG()
These are purely mathematical functions used to extract broad trends from numeric values.
- NULL Impact on AVG(): Because AVG() skips NULL values entirely, it does not factor them into the denominator (the divisor). If you have 4 numbers and 1 NULL, AVG() sums the 4 numbers and divides by 4, not 5.
-- Find total revenue and average order amount
SELECT SUM(total_amount) AS total_revenue, AVG(total_amount) AS average_order_value FROM sales;
Segmenting Data with GROUP BY
When you want to see these metrics broken down by a specific category (like average salary per department or total orders per customer), you must pair your aggregate function with a GROUP BY clause.
SELECT department_id,
COUNT(*) AS total_employees,
AVG(salary) AS average_salaryFROM employeesGROUP BY department_id;
The Golden Rule: Combining with GROUP BY
If you select an aggregate function alongside a normal column, you must include a GROUP BY clause listing that normal column, or your database will throw a critical syntax breakdown error.
-- ❌ THIS WILL FAIL:
SELECT department, AVG(salary) FROM employees;
-- THIS IS CORRECT:
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;