SQL Aggregate Functions
Aggregate functions turn many rows into one value: how many orders, total revenue, average salary, the cheapest product. They are the building blocks of every report and dashboard, and the start of GROUP BY, which applies them per group.
- Count rows with
COUNT(*),COUNT(column)andCOUNT(DISTINCT ...) - Total and average with
SUMandAVG - Find extremes with
MINandMAX - How aggregates treat NULL, and joining values with
GROUP_CONCAT
The five core aggregates
| Function | Returns | Skips NULL? |
|---|---|---|
COUNT(*) | number of rows | counts every row |
COUNT(col) | number of non-NULL values | yes |
SUM(col) | total | yes |
AVG(col) | average (sum ÷ count of non-NULL) | yes |
MIN(col) / MAX(col) | smallest / largest value | yes |
Sample data
| emp_id | first_name | dept_id | salary | commission | hire_date |
|---|---|---|---|---|---|
| 100 | Meera | NULL | 250000 | NULL | 2015-01-05 |
| 101 | Asha | 1 | 125000 | NULL | 2018-04-12 |
| 102 | Rahul | 1 | 98000 | NULL | 2019-07-01 |
| 103 | Priya | 1 | 72000 | NULL | 2021-01-18 |
| 104 | Karan | 1 | 72000 | NULL | 2022-08-29 |
| 105 | Vikram | 2 | 105000 | 12000 | 2017-11-20 |
| 106 | Neha | 2 | 54000 | 6000 | 2022-03-14 |
| 107 | Arjun | 2 | 48000 | 4500 | 2023-06-05 |
| 108 | Kavya | 3 | 88000 | NULL | 2020-09-09 |
| 109 | Rohan | 3 | 45000 | NULL | 2024-01-22 |
| 110 | Sneha | 4 | 78000 | NULL | 2019-02-25 |
| 111 | Amit | 1 | 25000 | NULL | 2025-06-02 |
COUNT
SELECT COUNT(*) AS employees, COUNT(dept_id) AS with_department, COUNT(commission) AS with_commission, COUNT(DISTINCT dept_id) AS departmentsFROM employees;| employees | with_department | with_commission | departments |
|---|---|---|---|
| 12 | 11 | 3 | 4 |
COUNT(*)counts all 12 rows, NULLs or not.COUNT(dept_id)skips the CEO, whosedept_idis NULL: 11.COUNT(commission)counts the 3 sales staff.COUNT(DISTINCT dept_id)counts different departments: 4.
SUM and AVG
SELECT SUM(salary) AS payroll, AVG(salary) AS avg_salary, ROUND(AVG(salary), 0) AS avg_roundedFROM employees;| payroll | avg_salary | avg_rounded |
|---|---|---|
| 1060000 | 88333.3333 | 88333 |
SUM(salary)adds the 12 salaries: 1,060,000.AVG(salary)divides that by 12. MySQL returns an exact decimal with four places: 88333.3333.ROUND(..., 0)rounds to a whole number for display.
MIN and MAX
MIN and MAX work on numbers, text (alphabetical) and dates:
SELECT MIN(salary) AS lowest_salary, MAX(salary) AS highest_salary, MIN(hire_date) AS first_hire, MAX(hire_date) AS latest_hire, MIN(first_name) AS first_alphabeticallyFROM employees;| lowest_salary | highest_salary | first_hire | latest_hire | first_alphabetically |
|---|---|---|---|---|
| 25000 | 250000 | 2015-01-05 | 2025-06-02 | Amit |
MAX(salary) tells you the highest salary, not who earns it. To get the whole row, use ORDER BY salary DESC LIMIT 1, a subquery, or a window function.
Aggregates with WHERE
WHERE filters rows before they are aggregated:
SELECT COUNT(*) AS engineers, SUM(salary) AS engineering_payroll, MAX(salary) AS top_salaryFROM employeesWHERE dept_id = 1;| engineers | engineering_payroll | top_salary |
|---|---|---|
| 5 | 392000 | 125000 |
WHERE dept_id = 1keeps the 5 Engineering employees.- The aggregates run on those 5 rows only.
Aggregates on expressions
Total revenue from all order lines, quantity × price:
SELECT SUM(oi.quantity * p.price) AS revenue, SUM(oi.quantity) AS units_sold, COUNT(DISTINCT oi.order_id) AS ordersFROM order_items oiJOIN products p ON p.product_id = oi.product_id;| revenue | units_sold | orders |
|---|---|---|
| 72262 | 38 | 10 |
- The join adds each product’s price to its order lines.
- For every line MySQL calculates
quantity * price, thenSUMadds them all. COUNT(DISTINCT oi.order_id)counts orders, not lines: 16 lines belong to 10 orders.
Aggregates and NULL
SELECT AVG(commission) AS avg_of_sellers, AVG(COALESCE(commission, 0)) AS avg_of_everyone, SUM(commission) AS totalFROM employees;| avg_of_sellers | avg_of_everyone | total |
|---|---|---|
| 7500.0000 | 1875.0000 | 22500 |
AVG(commission)ignores the 9 NULLs: 22,500 ÷ 3 = 7,500.COALESCE(commission, 0)turns NULL into 0, so the average is over all 12 rows: 22,500 ÷ 12 = 1,875.- Which answer is right depends on what NULL means in your data.
An aggregate over zero rows returns NULL (except COUNT, which returns 0):
SELECT COUNT(*) AS n, SUM(salary) AS total, MAX(salary) AS topFROM employeesWHERE dept_id = 5;| n | total | top |
|---|---|---|
| 0 | NULL | NULL |
GROUP_CONCAT: join values into one string
MySQL’s GROUP_CONCAT collects values into a comma-separated list:
SELECT GROUP_CONCAT(first_name ORDER BY first_name SEPARATOR ', ') AS engineering_teamFROM employeesWHERE dept_id = 1;| engineering_team |
|---|
| Amit, Asha, Karan, Priya, Rahul |
The standard SQL equivalent is STRING_AGG (PostgreSQL, SQL Server).
Mixing aggregates and plain columns
This query fails in MySQL 8.4, because first_name is not aggregated and there is no GROUP BY. Which of the 12 names should go next to the single maximum?
SELECT first_name, MAX(salary)FROM employees;ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'company.employees.first_name'; this is incompatible with sql_mode=only_full_group_by
Either aggregate every selected column, or add the plain columns to a GROUP BY. That rule comes from the default ONLY_FULL_GROUP_BY mode.
Exercise: Product statistics
In one row show the number of products, the cheapest price, the most expensive price and the total stock.
| products | cheapest | dearest | units_in_stock |
|---|---|---|---|
| 9 | 199 | 18999 | 780 |
Hint 1
COUNT(*), MIN(price), MAX(price), SUM(stock).
SELECT COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS dearest, SUM(stock) AS units_in_stockFROM products;| products | cheapest | dearest | units_in_stock |
|---|---|---|---|
| 9 | 199 | 18999 | 780 |
Exercise: Delivered orders
How many orders have the status ‘Delivered’, and what was the first and last delivered order date?
| delivered | first_date | last_date |
|---|---|---|
| 7 | 2025-01-05 | 2025-06-01 |
Hint 1
Filter with WHERE, then use COUNT, MIN and MAX.
SELECT COUNT(*) AS delivered, MIN(order_date) AS first_date, MAX(order_date) AS last_dateFROM ordersWHERE status = 'Delivered';| delivered | first_date | last_date |
|---|---|---|
| 7 | 2025-01-05 | 2025-06-01 |
Quick quiz
1. What is the difference between COUNT(*) and COUNT(email)?
Only COUNT(*) counts every row.
2. Values are 10, 20, NULL. What is AVG?
NULL is skipped: 30 ÷ 2.
3. What does SUM(salary) return when no rows match?
Aggregates other than COUNT return NULL on an empty set.
4. Which function returns the latest date?
MAX works on dates too.
Aggregate functions FAQ
What are aggregate functions in SQL?
Functions that compute one value from many rows: COUNT, SUM, AVG, MIN and MAX, plus others such as GROUP_CONCAT in MySQL.
Does COUNT count NULL values?
COUNT(*) counts every row. COUNT(column) counts only non-NULL values of that column.
Can I use aggregate functions in a WHERE clause?
No. WHERE filters individual rows before aggregation. Use HAVING to filter on aggregates, or a subquery.
Why does AVG return so many decimal places in MySQL?
AVG of integers returns an exact DECIMAL with four extra digits. Wrap it in ROUND(AVG(col), 2) to format it.