October 1, 2026

MySQL Aggregate Functions: COUNT, SUM, AVG, MIN, MAX with Examples

Introduction

When working with a MySQL database, sometimes you don't need to see every individual record.

For example:

• How many customers are registered?

• What is the total sales amount?

• What is the average product price?

• What is the cheapest product?

• What is the most expensive product?

• How many orders have been placed?

• What is the total salary of all employees?

Instead of calculating these values manually, MySQL provides Aggregate Functions.

Aggregate functions perform calculations on multiple rows and return a single result.

The most commonly used MySQL aggregate functions are:

• COUNT() – Counts records

• SUM() – Calculates the total

• AVG() – Calculates the average

• MIN() – Finds the minimum value

• MAX() – Finds the maximum value

In this tutorial, we will learn all five aggregate functions with simple examples, practical SQL queries, real-world business examples, common mistakes, and practice exercises.

---

1. What Are MySQL Aggregate Functions?

An aggregate function performs a calculation on a set of rows and returns one result.

For example, suppose we have a products table:

id	product_name	price
1	Laptop	55000
2	Mobile	25000
3	Keyboard	1500
4	Monitor	12000
5	Mouse	800

If we want to calculate the total price of all products, we can use:

SELECT SUM(price) AS total_price
FROM products;

Result:

total_price
94300

Instead of returning five product records, MySQL returns one calculated value.

---

2. Why Are Aggregate Functions Important?

Aggregate functions are extremely useful for reports, dashboards, analytics, and business applications.

For example, an e-commerce website may need to display:

Total Products: 1,250
Total Sales: ₹8,50,000
Average Order Value: ₹1,850
Minimum Product Price: ₹199
Maximum Product Price: ₹1,25,000

These values can be generated directly from the database using aggregate functions.

They are commonly used in:

• E-commerce websites

• CRM systems

• Accounting software

• Employee management systems

• School management systems

• Inventory systems

• Banking applications

• Sales dashboards

• Business reports

---

3. MySQL Aggregate Functions List

Here are the five main aggregate functions covered in this tutorial:

Function	Purpose	Example
COUNT()	Counts rows/values	COUNT(id)
SUM()	Calculates total	SUM(price)
AVG()	Calculates average	AVG(price)
MIN()	Finds smallest value	MIN(price)
MAX()	Finds largest value	MAX(price)

Let's understand each one.

---

4. COUNT() Function in MySQL

The COUNT() function is used to count records or values.

It is one of the most commonly used aggregate functions.

Basic Syntax

SELECT COUNT(column_name)
FROM table_name;

Example:

SELECT COUNT(id) AS total_products
FROM products;

Result:

total_products
5

This tells us that the products table contains 5 records with a non-NULL id.

---

5. COUNT(*) in MySQL

You can also use COUNT(*) to count rows.

SELECT COUNT(*) AS total_products
FROM products;

Result:

total_products
5

COUNT(*) counts rows, including rows where individual columns contain NULL.

For counting all rows in a table, COUNT(*) is generally the clearest choice.

---

6. COUNT(column) vs COUNT(*)

This is an important concept for beginners.

Suppose we have a customers table:

id	name	phone
1	Rahul	9876543210
2	Amit	9876543211
3	Priya	NULL
4	Neha	9876543213

Now:

SELECT COUNT(*) AS total_customers
FROM customers;

Result:

4

But:

SELECT COUNT(phone) AS customers_with_phone
FROM customers;

Result:

3

Why?

Because COUNT(column_name) does not count NULL values.

So:

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

---

7. COUNT() with WHERE

You can combine COUNT() with WHERE to count specific records.

For example, suppose we want to count products costing more than ₹10,000.

SELECT COUNT(*) AS expensive_products
FROM products
WHERE price > 10000;

Result:

expensive_products
3

This is useful when creating business reports.

---

8. Real-Time Example: Count Registered Customers

Imagine you are building a CRM system.

Your customers table contains thousands of customers.

You want to display:

Total Registered Customers: 5,420

Query:

SELECT COUNT(*) AS total_customers
FROM customers;

Result:

total_customers
5420

This value can then be displayed on an admin dashboard.

---

9. SUM() Function in MySQL

The SUM() function calculates the total of a numeric column.

Basic Syntax

SELECT SUM(column_name)
FROM table_name;

Example:

SELECT SUM(price) AS total_price
FROM products;

Result:

total_price
94300

The function adds all non-NULL numeric values.

---

10. SUM() with Sales Data

Suppose we have an orders table:

id	customer_id	amount
1	101	1500
2	102	2500
3	103	1200
4	104	3000

To calculate total sales:

SELECT SUM(amount) AS total_sales
FROM orders;

Result:

total_sales
8200

So the total sales amount is ₹8,200.

---

11. SUM() with WHERE

You can calculate a specific type of total using WHERE.

For example, suppose the orders table has a status column:

id	amount	status
1	1500	Completed
2	2500	Completed
3	1200	Cancelled
4	3000	Completed

To calculate only completed sales:

SELECT SUM(amount) AS completed_sales
FROM orders
WHERE status = 'Completed';

Result:

completed_sales
7000

This is a very common real-world use case.

---

12. AVG() Function in MySQL

The AVG() function calculates the average value of a numeric column.

Basic Syntax

SELECT AVG(column_name)
FROM table_name;

Example:

SELECT AVG(price) AS average_price
FROM products;

Suppose the prices are:

55000
25000
1500
12000
800

MySQL calculates the average automatically.

---

13. Understanding Average Calculation

Suppose we have these salaries:

30000
40000
50000
60000

The average is:

(30000 + 40000 + 50000 + 60000) / 4

Which equals:

45000

MySQL can calculate this directly:

SELECT AVG(salary) AS average_salary
FROM employees;

Result:

average_salary
45000

---

14. AVG() with WHERE

You can calculate the average for a specific set of records.

For example, average price of products above ₹10,000:

SELECT AVG(price) AS average_price
FROM products
WHERE price > 10000;

This is useful for targeted business analysis.

---

15. Real-Time Example: Average Order Value

Suppose an online store has an orders table:

id	customer_id	amount
1	101	1500
2	102	2500
3	103	1000
4	104	5000

To calculate the average order value:

SELECT AVG(amount) AS average_order_value
FROM orders;

Result:

average_order_value
2500

This can be useful for an e-commerce dashboard.

---

16. MIN() Function in MySQL

The MIN() function returns the smallest value from a column.

Basic Syntax

SELECT MIN(column_name)
FROM table_name;

Example:

SELECT MIN(price) AS lowest_price
FROM products;

Result:

lowest_price
800

This tells us the cheapest product price is ₹800.

---

17. Real-Time Example: Cheapest Product

Suppose an online store contains thousands of products.

To find the lowest product price:

SELECT MIN(price) AS minimum_price
FROM products;

Result:

800

This is useful for:

• Price reports

• Product analytics

• Pricing dashboards

• Inventory reports

---

18. MIN() with WHERE

You can also find the minimum value from a filtered set of records.

For example:

SELECT MIN(price) AS minimum_laptop_price
FROM products
WHERE category = 'Laptop';

This finds the cheapest laptop price.

---

19. MAX() Function in MySQL

The MAX() function returns the largest value from a column.

Basic Syntax

SELECT MAX(column_name)
FROM table_name;

Example:

SELECT MAX(price) AS highest_price
FROM products;

Result:

highest_price
55000

This tells us that the highest product price is ₹55,000.

---

20. Real-Time Example: Highest Employee Salary

Suppose an employees table contains:

id	name	salary
1	Rahul	30000
2	Amit	45000
3	Priya	55000
4	Neha	40000

To find the highest salary:

SELECT MAX(salary) AS highest_salary
FROM employees;

Result:

highest_salary
55000

---

21. MAX() with WHERE

You can use WHERE to find the maximum value for a specific category.

For example:

SELECT MAX(price) AS highest_laptop_price
FROM products
WHERE category = 'Laptop';

This returns the highest-priced laptop.

---

22. Using COUNT, SUM, AVG, MIN and MAX Together

One of the most useful features of aggregate functions is that you can use multiple functions in the same query.

For example:

SELECT
    COUNT(*) AS total_products,
    SUM(price) AS total_value,
    AVG(price) AS average_price,
    MIN(price) AS lowest_price,
    MAX(price) AS highest_price
FROM products;

Result:

total_products	total_value	average_price	lowest_price	highest_price
5	94300	18860	800	55000

This single query can generate a basic product summary report.

---

23. Real-Time E-Commerce Product Report

Imagine an e-commerce admin dashboard.

You want to show:

Total Products
Total Inventory Value
Average Product Price
Cheapest Product
Most Expensive Product

Instead of running five separate queries, you can use:

SELECT
    COUNT(*) AS total_products,
    SUM(price) AS total_inventory_value,
    AVG(price) AS average_price,
    MIN(price) AS cheapest_price,
    MAX(price) AS most_expensive_price
FROM products;

The application can then display the result in dashboard cards.

Example:

-----------------------------------------
Total Products        1,250
Total Inventory       ₹85,40,000
Average Price         ₹6,832
Cheapest Product      ₹199
Most Expensive        ₹1,25,000
-----------------------------------------

This is one of the most practical uses of aggregate functions.

---

24. Aggregate Functions with WHERE

Aggregate functions become even more useful when combined with WHERE.

For example, suppose you only want information about active products.

SELECT
    COUNT(*) AS total_products,
    SUM(price) AS total_value,
    AVG(price) AS average_price,
    MIN(price) AS minimum_price,
    MAX(price) AS maximum_price
FROM products
WHERE status = 'Active';

Now all five calculations are performed only on active products.

---

25. Real-Time Sales Report

Suppose your orders table contains:

id	amount	status
1	1500	Completed
2	2500	Completed
3	1000	Cancelled
4	5000	Completed
5	2000	Completed

You want a report for completed orders only.

Query:

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_order_value,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders
WHERE status = 'Completed';

This can provide information such as:

Total Orders: 4
Total Sales: ₹11,000
Average Order Value: ₹2,750
Smallest Order: ₹1,500
Largest Order: ₹5,000

This type of query is commonly used for sales dashboards.

---

26. Aggregate Functions and NULL Values

Understanding NULL is important when using aggregate functions.

Suppose we have:

id	salary
1	30000
2	40000
3	NULL
4	50000

If we run:

SELECT AVG(salary) AS average_salary
FROM employees;

MySQL does not treat NULL as zero.

It calculates the average using the available non-NULL salary values.

Conceptually:

(30000 + 40000 + 50000) / 3

not:

(30000 + 40000 + 0 + 50000) / 4

This distinction is important when analyzing real-world data.

---

27. SUM() and NULL Values

SUM() also ignores NULL values.

For example:

1000
2000
NULL
3000

The result of:

SELECT SUM(amount)
FROM orders;

is:

6000

The NULL value is not added as zero.

---

28. MIN() and MAX() with NULL

MIN() and MAX() also ignore NULL values when calculating the result.

For example:

100
200
NULL
500

Query:

SELECT
    MIN(price) AS minimum_price,
    MAX(price) AS maximum_price
FROM products;

Result:

minimum_price = 100
maximum_price = 500

---

29. Formatting Decimal Results with ROUND()

Sometimes AVG() can return a decimal value with many digits.

For example:

SELECT AVG(price) AS average_price
FROM products;

You might get:

18860.0000

You can use ROUND() to control the number of decimal places:

SELECT ROUND(AVG(price), 2) AS average_price
FROM products;

Here:

2

means two decimal places.

Example:

18860.00

This is useful when displaying financial values.

---

30. Real-Time Employee Salary Report

Suppose an organization has an employees table.

You want to know:

• Number of employees

• Total salary

• Average salary

• Lowest salary

• Highest salary

Query:

SELECT
    COUNT(*) AS total_employees,
    SUM(salary) AS total_salary,
    ROUND(AVG(salary), 2) AS average_salary,
    MIN(salary) AS minimum_salary,
    MAX(salary) AS maximum_salary
FROM employees;

This query can be used to create a basic HR salary report.

---

31. Real-Time Customer Order Report

Suppose you want to analyze customer orders.

Your orders table contains:

id
customer_id
amount
status
created_at

For completed orders:

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    ROUND(AVG(amount), 2) AS average_order_value,
    MIN(amount) AS minimum_order,
    MAX(amount) AS maximum_order
FROM orders
WHERE status = 'Completed';

This provides a quick overview of the sales performance.

---

32. Using Aliases with Aggregate Functions

Without aliases, MySQL may return column names such as:

COUNT(*)
SUM(amount)
AVG(amount)

It is better to give meaningful names.

Instead of:

SELECT COUNT(*), SUM(amount)
FROM orders;

Use:

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales
FROM orders;

This makes the result easier to understand and easier to use in PHP, CodeIgniter, APIs, and dashboards.

---

33. Common Mistakes with Aggregate Functions

Mistake 1: Using SUM() on Text Columns

Incorrect:

SELECT SUM(name)
FROM customers;

SUM() is intended for numeric values.

Use it with columns such as:

price
salary
amount
quantity
total

---

Mistake 2: Assuming NULL is Zero

Consider:

1000
2000
NULL

Do not assume NULL automatically means 0.

NULL means the value is missing or unknown.

Aggregate functions generally ignore NULL values.

---

Mistake 3: Using COUNT(column) When You Need All Rows

Suppose:

SELECT COUNT(phone)
FROM customers;

This counts customers having a non-NULL phone value.

If you want the total number of rows:

SELECT COUNT(*)
FROM customers;

---

Mistake 4: Forgetting an Alias

This:

SELECT SUM(amount)
FROM orders;

works, but this is easier to understand:

SELECT SUM(amount) AS total_sales
FROM orders;

---

Mistake 5: Expecting AVG() to Include NULL as Zero

For:

10
20
NULL

AVG() calculates the average of available numeric values, not:

(10 + 20 + 0) / 3

Understanding NULL is essential when working with real-world data.

---

34. Aggregate Functions Quick Reference

Function	Purpose	Example
COUNT(*)	Count rows	COUNT(*)
COUNT(column)	Count non-NULL values	COUNT(phone)
SUM()	Calculate total	SUM(amount)
AVG()	Calculate average	AVG(price)
MIN()	Find minimum	MIN(price)
MAX()	Find maximum	MAX(price)

Basic Examples

SELECT COUNT(*) FROM products;

SELECT SUM(price) FROM products;

SELECT AVG(price) FROM products;

SELECT MIN(price) FROM products;

SELECT MAX(price) FROM products;

All Together

SELECT
    COUNT(*) AS total_records,
    SUM(price) AS total_price,
    AVG(price) AS average_price,
    MIN(price) AS minimum_price,
    MAX(price) AS maximum_price
FROM products;

---

35. Practice Exercises

Try solving these queries yourself.

Exercise 1

Find the total number of customers.

-- Write your query here

Exercise 2

Find the total amount of all orders.

-- Write your query here

Exercise 3

Find the average product price.

-- Write your query here

Exercise 4

Find the cheapest product price.

-- Write your query here

Exercise 5

Find the most expensive product price.

-- Write your query here

Exercise 6

Find the total number of completed orders.

-- Write your query here

Exercise 7

Find the total revenue from completed orders.

-- Write your query here

Exercise 8

Find the average salary of employees.

-- Write your query here

Exercise 9

Create one query that returns:

Total Employees
Total Salary
Average Salary
Minimum Salary
Maximum Salary

---

36. Practical Query Challenge

Imagine you are building an e-commerce admin dashboard.

Your orders table contains:

id
customer_id
amount
status
created_at

You need to generate a sales summary containing:

Total Completed Orders
Total Revenue
Average Order Value
Smallest Order
Largest Order

Write a single SQL query using:

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

The solution would be:

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    ROUND(AVG(amount), 2) AS average_order_value,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders
WHERE status = 'Completed';

This is a practical example of how aggregate functions can be used in a real business application.

---

37. Important Things to Remember

Before moving to the next MySQL topic, remember these points:

1. COUNT() counts records or non-NULL values.

2. COUNT(*) counts rows.

3. SUM() calculates the total.

4. AVG() calculates the average.

5. MIN() finds the smallest value.

6. MAX() finds the largest value.

7. Aggregate functions normally ignore NULL values.

8. WHERE can be used to filter the rows before aggregation.

9. Use aliases such as AS total_sales to make results readable.

10. Multiple aggregate functions can be used in one query.

11. ROUND() can be used when you need cleaner decimal output.

12. Aggregate functions are very useful for reports and dashboards.

---

Conclusion

MySQL Aggregate Functions make it easy to calculate useful information from large amounts of data.

The five most important functions are:

COUNT() → Count records
SUM()   → Calculate total
AVG()   → Calculate average
MIN()   → Find minimum
MAX()   → Find maximum

For example:

SELECT
    COUNT(*) AS total_orders,
    SUM(amount) AS total_sales,
    AVG(amount) AS average_order,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders;

With just one SQL query, you can generate important business information that can be used in dashboards, reports, admin panels, APIs, PHP applications, and CodeIgniter projects.

Once you understand aggregate functions, you are ready to move one step further and work with calculations based on categories and groups of records.

---

Next MySQL Tutorial

In the next tutorial, we will learn:

MySQL GROUP BY & HAVING: Complete Guide to Data Grouping and Reports

We will learn how to group records, calculate results for each group, filter grouped results, and create practical reporting queries.

For example:

SELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

That topic will be covered separately in the next blog.