MySQL: A Detailed Explanation of MySQL Aggregate Functions…
Last updated: 2026-08-26
Aggregate functions are at the heart of data analysis—statistics, grouping, and summarization all rely on them.
This lesson provides an in-depth explanation of aggregate functions and grouped queries.
graph TB
A[Aggregate Query Process] --> B[FROM Data Sources]
B --> C[WHERE Filter Rows]
C --> D[GROUP BY Grouping]
D --> E[Aggregate Functions<br/>COUNT/SUM/AVG/MAX/MIN]
E --> F[HAVING Filter Group]
F --> G[SELECT Output]
G --> H[ORDER BY Sort]
H --> I[WITH ROLLUP Summary]
1. What You'll Learn
- COUNT/SUM/AVG/MAX/MIN usage
- GROUP BY: Grouped Statistics
- HAVING Filter Groups
- WITH ROLLUP summary rows
- The Relationship Between Aggregate Functions and NULL
2. Real-Life Stories Behind the Report Statistics
(1) Pain Point: Excel is too slow for data analysis
An operations manager needs to compile statistics on the number of employees in each department, the average salary, and the highest salary.
Using Excel requires writing multiple formulas, and the calculations have to be redone whenever the data is updated.
(2) Solving Aggregate Functions
SQL
SELECT
department,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary,
MAX(salary) AS max_salary
FROM employees
GROUP BY department;
3. Aggregate Functions
(1) COUNT
SQL
-- Count the total number of lines
SELECT COUNT(*) FROM employees;
-- Count non-NULL rows
SELECT COUNT(email) FROM employees;
-- Count the number of unique entries
SELECT COUNT(DISTINCT department) FROM employees;
⚠️ Note:
COUNT(*) counts all rows, while COUNT(col) counts rows where col is not NULL.
(2) SUM/AVG: Sum/Average
SQL
-- Total Wages
SELECT SUM(salary) FROM employees;
-- Average Wage
SELECT AVG(salary) FROM employees;
-- Conditional Summation
SELECT SUM(amount) FROM orders WHERE status = 'paid';
(3) MAX/MIN Maximum/Minimum
SQL
-- Highest Salary
SELECT MAX(salary) FROM employees;
-- Minimum Wage
SELECT MIN(salary) FROM employees;
-- Earliest/Latest Date
SELECT MIN(hire_date) AS earliest, MAX(hire_date) AS latest FROM employees;
4. GROUP BY
▶ Example: Single-Field Grouping
SQL
-- By Department
SELECT
department,
COUNT(*) AS emp_count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
Output:
TEXT
📖 Display only
+-------------+-----------+-----------+
| department | emp_count | avg_salary|
+-------------+-----------+-----------+
| Engineering | 15 | 8500.00 |
| Sales | 10 | 6000.00 |
| Marketing | 8 | 5500.00 |
| HR | 5 | 5000.00 |
+-------------+-----------+-----------+
▶ Example: Multi-field grouping
SQL
-- By department and job title
SELECT
department,
job_title,
COUNT(*) AS count,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department, job_title
ORDER BY department, avg_salary DESC;
Output:
TEXT
📖 Display only
Output displayed
▶ Example: Grouping by Expression
Output:
TEXT
📖 Display only
+------+-------------+--------------+
| year | order_count | total_amount |
+------+-------------+--------------+
| 5 | 5 | 5 |
+------+-------------+--------------+
1 row in set
+-----------+-------+
| age_group | count |
+-----------+-------+
| 5 | 5 |
+-----------+-------+
1 row in set
SQL
-- Orders by Year
SELECT
YEAR(order_date) AS year,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY YEAR(order_date);
-- Statistics by Age Group
SELECT
CASE
WHEN age < 18 THEN 'Under 18'
WHEN age BETWEEN 18 AND 30 THEN '18-30'
WHEN age BETWEEN 31 AND 50 THEN '31-50'
ELSE 'Over 50'
END AS age_group,
COUNT(*) AS count
FROM users
GROUP BY age_group;
Output:
TEXT
📖 Display only
Output displayed
5. HAVING Filtering and Grouping
WHERE filters rows; HAVING filters groups.
▶ Example: Using HAVING
Output:
TEXT
📖 Display only
+------------+------------+
| department | avg_salary |
+------------+------------+
| 30.00 | 30.00 |
+------------+------------+
1 row in set
+-------------+-------------+--------------+
| customer_id | order_count | total_amount |
+-------------+-------------+--------------+
| 5 | 5 | 5 |
+-------------+-------------+--------------+
1 row in set
+------------+------------+
| department | avg_salary |
+------------+------------+
| 30.00 | 30.00 |
+------------+------------+
1 row in set
SQL
-- Find the average wage > 6000 the department
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 6000;
-- Find the number of orders > 10 's customers
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING order_count > 10;
-- WHERE + GROUP BY + HAVING Combination
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2024-01-01' -- Filter the rows first
GROUP BY department -- Regroup
HAVING avg_salary > 6000; -- Final Filter Group
Output:
TEXT
📖 Display only
Output displayed
6. WITH ROLLUP Summary
▶ Example: ROLLUP Summary Rows
SQL
-- Automatically generate total rows
SELECT
department,
COUNT(*) AS emp_count,
SUM(salary) AS total_salary
FROM employees
GROUP BY department WITH ROLLUP;
Output:
TEXT
📖 Display only
+-------------+-----------+--------------+
| department | emp_count | total_salary |
+-------------+-----------+--------------+
| Engineering | 15 | 127500.00 |
| Sales | 10 | 60000.00 |
| Marketing | 8 | 44000.00 |
| HR | 5 | 25000.00 |
| NULL | 38 | 256500.00 | -- Total
+-------------+-----------+--------------+
7. Aggregate Functions and NULL
| Function | NULL Handling |
|---|---|
COUNT(*) |
Contains NULL rows |
COUNT(col) Exclude NULL |
|
SUM(col) ignore NULL |
|
AVG(col) |
Ignore NULL (denominator does not contain NULL) |
MAX(col) ignore NULL |
|
MIN(col) ignore NULL |
▶ Example: Handling NULL Values
SQL
-- COUNT(*) vs COUNT(email)
SELECT
COUNT(*) AS total_rows,
COUNT(email) AS has_email,
COUNT(*) - COUNT(email) AS no_email
FROM users;
-- AVG Ignore NULL
SELECT AVG(salary) FROM employees; -- NULL Not included in the calculation
Output:
TEXT
📖 Display only
Output displayed
❓ FAQ
Q Can WHERE and HAVING be used together?
A Yes. Execution order: WHERE (filter rows) → GROUP BY (group) → HAVING (filter groups) → SELECT → ORDER BY.
Q Do the fields in the GROUP BY clause have to be included in the SELECT clause?
A In MySQL Strict Mode, non-aggregate fields in the SELECT clause must be included in the GROUP BY clause.
Q Which is faster, COUNT(*) or COUNT(1)?
A They are equally fast. The MySQL optimizer automatically selects the best approach.
Q Can aggregate functions be nested?
A They cannot be nested directly
AVG(SUM(...)). Use subqueries: SELECT AVG(total) FROM (SELECT SUM(amount) AS total FROM orders GROUP BY customer_id) t.📖 Summary
- COUNT/SUM/AVG/MAX/MIN are the five major aggregate functions
- GROUP BY Groups by field; multiple fields can be combined
- HAVING filters by group (aggregate functions can be used), WHERE filters by row (aggregate functions cannot be used)
- WITH ROLLUP Automatically generates summary rows
- NULL is ignored in aggregate functions (except for COUNT(*))
📝 Exercises
-
Basic Problem (Difficulty: ⭐): Calculate the number of employees and the average salary for each department.
-
Advanced Problem (Difficulty ⭐⭐): Find customers whose total order amount is greater than 10,000, and sort them in descending order by total amount.
-
Challenge (Difficulty: ⭐⭐⭐): Calculate the number of orders, total amount, and average amount for each month, and use
WITH ROLLUPto add annual summary rows.
