Aggregate Functions

Aggregate functions are used to perform calculations on a set of rows and return a single summarized value.

They are essential for analytics, reporting, and business insights. Almost every real-world SQL query uses aggregate functions in some form.

What Does “Aggregate” Mean?

To aggregate means to collect and summarize. Instead of looking at individual rows, aggregate functions look at many rows together and produce one result.

For example:

• Total number of employees
• Average salary
• Highest or lowest value
• Total sales amount

Common Aggregate Functions

Example Table

We will continue using the employees table:

+----+----------+------------+--------+
| id | name     | department | salary |
+----+----------+------------+--------+
| 1  | Alice    | IT         | 75000  |
| 2  | Bob      | IT         | 72000  |
| 3  | Charlie  | HR         | 60000  |
| 4  | Diana    | Finance    | 70000  |
| 5  | Evan     | HR         | 58000  |
+----+----------+------------+--------+
  

COUNT Function in Detail

The COUNT() function counts rows. There are different ways to use it.

SELECT COUNT(*) FROM employees;
  

This counts all rows, including rows with NULL values.

Counting a specific column:

SELECT COUNT(salary) FROM employees;
  

This counts only rows where salary is NOT NULL.

COUNT with DISTINCT

You can count unique values using DISTINCT.

Example: Count unique departments.

SELECT COUNT(DISTINCT department)
FROM employees;
  

This tells you how many different departments exist.

SUM and AVG in Practice

SUM and AVG are commonly used in financial and analytical queries.

Total salary paid:

SELECT SUM(salary) AS total_salary
FROM employees;
  

Average salary:

SELECT AVG(salary) AS average_salary
FROM employees;
  

MIN and MAX in Practice

MIN and MAX help identify boundaries in your data.

Lowest and highest salary:

SELECT MIN(salary) AS lowest_salary,
       MAX(salary) AS highest_salary
FROM employees;
  

Important Rule: Aggregates Return One Row

Aggregate functions always return a single summarized row (unless grouping is applied).

This means you cannot freely mix aggregate functions with normal columns.

❌ Incorrect:

SELECT name, AVG(salary)
FROM employees;
  

This will cause an error because name is not aggregated.

✅ Correct usage of this concept will be covered in the next lesson.

Common Beginner Mistakes

• Using aggregate functions on text columns
• Forgetting DISTINCT when counting unique values
• Mixing aggregates with non-aggregated columns
• Assuming aggregate functions change table data

Aggregate functions only affect query results. They never modify stored data.

Why Aggregate Functions Matter

Aggregate functions help you:

• Summarize large datasets
• Generate reports and metrics
• Answer business questions
• Prepare data for dashboards

What’s Next?

In the next lesson, we will learn GROUP BY, which allows aggregate functions to work per category instead of on the entire table.