Skip to main content
MySQL Basics
CHAPTER 10 Beginner

Aggregate Functions in MySQL

Updated: May 16, 2026
6 min read

# CHAPTER 10

Aggregate Functions in MySQL

1. Introduction

If you have an orders table with 10 million transactions, scrolling through individual rows is impossible. Business leaders do not want to see 10 million rows; they want summaries. "How much total money did we make today?" "What is the average order size?" "How many total users do we have?" To answer these mathematical questions without downloading the entire database into Excel, we use Aggregate Functions. In this chapter, we will turn our database into a high-speed calculator.

2. Learning Objectives

By the end of this chapter, you will be able to:
  • Understand the purpose of an Aggregate Function.
  • Use COUNT() to tally total rows.
  • Use SUM() to calculate financial totals.
  • Use AVG() to find the mathematical mean.
  • Use MAX() and MIN() to find extremes.
  • Create aliases using the AS keyword.

3. What is an Aggregate Function?

Standard queries run row-by-row. If you SELECT first_name, and there are 50 rows, MySQL outputs 50 rows. An Aggregate Function takes multiple rows, performs a mathematical calculation on them, and collapses them down into a single, summary row.

4. Counting Rows (COUNT)

The most frequently used aggregate function. It simply counts how many rows exist.
sql
1234567 
-- How many total employees work at the company?
SELECT COUNT(*) FROM employees;
-- Output: 1 (Single row showing the number 500)

-- You can combine aggregates with WHERE clauses!
-- How many employees work in the IT department?
SELECT COUNT(*) FROM employees WHERE department = 'IT';

*(Note: COUNT(*) counts all rows. COUNT(column_name) counts only the rows where that specific column is NOT NULL!)*

5. Sum and Average (SUM, AVG)

Used exclusively on numerical columns (INT, DECIMAL).
sql
12345 
-- What is the total payroll cost for the company?
SELECT SUM(salary) FROM employees;

-- What is the average salary in the Sales department?
SELECT AVG(salary) FROM employees WHERE department = 'Sales';

6. Maximum and Minimum (MAX, MIN)

Used to find the extreme highest or lowest values in a column. It works on numbers, dates, and even alphabetically on text!
sql
12345 
-- What is the highest salary paid in the company?
SELECT MAX(salary) FROM employees;

-- Who is the newest employee? (The highest/latest date)
SELECT MAX(hire_date) FROM employees;

7. Renaming Output Columns (AS Keyword)

When you run SELECT SUM(salary), the column header in the output will literally be named SUM(salary). This looks terrible and is hard for PHP or Python applications to read. We use the AS keyword to create an Alias (a temporary, friendly name for the output).
sql
1234 
SELECT SUM(salary) AS total_monthly_payroll 
FROM employees;

-- Now, your backend code can easily request the 'total_monthly_payroll' variable!

8. Mini Project: Sales Analytics Dashboard

Let's generate the 4 key metrics required for the daily executive dashboard in a single query!
sql
123456789101112 
SELECT 
    COUNT(*) AS total_transactions,
    SUM(order_total) AS gross_revenue,
    AVG(order_total) AS average_order_value,
    MAX(order_total) AS biggest_sale_today
FROM orders 
WHERE order_date = '2024-10-31';

-- Output:
-- total_transactions | gross_revenue | average_order_value | biggest_sale_today
-- -------------------|---------------|---------------------|-------------------
-- 1542               | 85000.50      | 55.12               | 1200.00

*Notice how we executed four massive mathematical calculations on 1,500 rows, and it collapsed beautifully into a single, highly readable row!*

9. Common Mistakes

  • Mixing Aggregates with Standard Columns: This is the #1 mistake beginners make.
SELECT firstname, MAX(salary) FROM employees; You are asking MySQL to output 500 individual first names, but only 1 maximum salary. The engine doesn't know how to format this. It will either crash (in Strict Mode) or output a completely random first name next to the max salary. (We learn how to fix this in Chapter 11).

10. Best Practices

  • Let the Database do the Math: Never use SELECT * to download 10,000 rows into PHP or Python just so you can run a for loop to add up the prices. MySQL is written in highly optimized C++. It can calculate the SUM() of a million rows millions of times faster than your backend web code can.

11. Exercises

  1. 1. Write a query to find the lowest price (MIN) of any product currently in the products table.
  1. 2. Rewrite the previous query to rename the output column to cheapestitem.

12. MCQ Quiz with Answers

Question 1

What is the fundamental difference between COUNT(*) and COUNT(email)?

Question 2

Why do developers use the AS keyword when writing Aggregate queries?

13. Interview Questions

  • Q: Explain why executing SELECT department, SUM(salary) FROM employees; will cause an error in modern SQL environments.
  • Q: Discuss the performance benefits of executing COUNT(*) natively inside the database versus downloading the data and counting the array size in the application's backend language (e.g., PHP).

14. FAQs

Q: Does SUM() round decimal numbers? A: No, SUM() calculates the exact values based on the column's data type. If your column is DECIMAL(10,2), the sum will be perfectly accurate. If you use FLOAT, you might see microscopic rounding errors.

15. Summary

Aggregate functions elevate SQL from a simple data retrieval tool into a powerful analytical engine. By utilizing COUNT, SUM, and AVG, you can offload heavy mathematical processing to the database layer, generating instantaneous executive reports and financial metrics.

16. Next Chapter Recommendation

In this chapter, we found the total payroll for the *entire* company. But what if the CEO wants to see the total payroll *broken down by each department*? We cannot do this with standard Aggregates alone. In Chapter 11: GROUP BY and HAVING Clauses, we will unlock the ultimate reporting feature of SQL.

Finish this Chapter

Save your progress on your learning path and prepare for coding interview challenges.

Previous Chapter SQL Operators and Conditions
Next Chapter GROUP BY and HAVING Clauses

Discussion

Join the discussion

Log in or create a free account to participate.

Sort: ·

Explore More

📖 Related Tutorials 6

❓ Related Quizzes 6