MySQL Aggregate Functions

Introduction

In this chapter, we will learn about aggregate functions in MySQL. Aggregate functions are used to perform calculations on a set of values and return a single value. They are often used in combination with the GROUP BY clause to group rows that share a common attribute. The most commonly used aggregate functions in MySQL include COUNT(), SUM(), AVG(), MIN(), and MAX(). We will cover the syntax for these functions, examples of their usage, and important considerations for using them in MySQL.

Common Aggregate Functions

1. COUNT(): Returns the number of rows that match a specified condition.
2. SUM(): Returns the total sum of a numeric column.
3. AVG(): Returns the average value of a numeric column.
4. MIN(): Returns the smallest value of a column.
5. MAX(): Returns the largest value of a column.

Syntax

The basic syntax for using aggregate functions in MySQL is:

SELECT AGGREGATE_FUNCTION(column_name)
FROM table_name
[WHERE condition]
[GROUP BY column_name];

Examples

Let’s go through examples for each aggregate function using the employees table.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Rahul', 'Sharma', 'Sales', 50000.00),
('Priya', 'Singh', 'Marketing', 60000.00),
('Amit', 'Kumar', 'Sales', 55000.00),
('Neha', 'Verma', 'IT', 58000.00),
('Sahil', 'Mehta', 'IT', 62000.00),
('Kiran', 'Patel', 'Sales', 53000.00);

2. COUNT()

Example: Count the Number of Employees

SELECT COUNT(*) AS employee_count FROM employees;

Output

3. SUM()

Example: Calculate the Total Salary

SELECT SUM(salary) AS total_salary FROM employees;

Output

4. AVG()

Example: Calculate the Average Salary

SELECT AVG(salary) AS average_salary FROM employees;

Output

5. MIN()

Example: Find the Minimum Salary

SELECT MIN(salary) AS minimum_salary FROM employees;

Output

6. MAX()

Example: Find the Maximum Salary

SELECT MAX(salary) AS maximum_salary FROM employees;

Output

Using Aggregate Functions with GROUP BY

Aggregate functions are often used with the GROUP BY clause to group rows that share a common attribute and perform calculations on each group.

Example: Calculate the Total Salary by Department

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Output

Example: Calculate the Average Salary by Department

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department;

Output

Important Considerations

• NULL Values: Aggregate functions ignore NULL values, except for COUNT(*), which counts all rows, including those with NULL values.
• GROUP BY Clause: When using aggregate functions with the GROUP BY clause, ensure that all non-aggregated columns are included in the GROUP BY clause.
• Performance: Aggregate functions can impact performance, especially on large datasets. Consider indexing columns used in aggregate functions to improve performance.

Conclusion

Aggregate functions in MySQL are powerful tools for performing calculations on sets of values and returning single summary values. This chapter covered the syntax for using common aggregate functions, provided examples of their usage, and discussed important considerations.