MySQL Aggregate Functions – MIN() and MAX()

Mysql Min And Max

In this tutorial, we will learn about the MIN() and MAX() aggregate functions. Suppose you want to find the highest-paid employee in your company. Won’t it be very easy if there was a function which you could use directly to get the result? MySQL MAX() does exactly that for you. The MIN() function does the exact opposite i.e. it will tell you the employee with the least salary.


What Are Aggregate Functions?

Before we move on to the implementation of the functions, it is important to understand what aggregate functions are. Aggregate Functions perform operations on multiple values of a column and return a single value. Examples of Aggregate functions are MIN(), MAX(), SUM(), COUNT(), AVG(), etc.

The generic syntax of aggregate functions is as follows:

function_name(expression)

Where function_name can be any one of the aggregate functions and expression contains column_names.

MAX() is an aggregate function which returns the maximum value in a column. Whereas, MIN() is an aggregate function which returns the smallest value in the given column.


Syntax of MySQL MIN() and MAX()

The syntax for MAX() is as follows,

SELECT MAX(column_name) FROM table_name; 

The syntax for MIN() is as follows,

SELECT MIN(column_name) FROM table_name;

Note that, you may add clauses like WHERE, LIKE, BETWEEN, IN and so on, in the above syntax.


Examples of MySQL MIN() and MAX()

Consider the below Employee table.

Aggregate Employee Table
Employee Table

1. Simple Example of MySQL MIN() and MAX()

Let us find out the maximum salary paid to any employee in the employee table. We do so using the below query,

SELECT MAX(Salary) FROM Employee;

And we get the output as,

Max Example 1

As you can see, the output tells us the maximum value in the Salary column, which is the highest amount paid as salary in the company. How about finding the least value in the salary column? The query is,

SELECT MIN(Salary) FROM Employee;

We get the output as,

Min Example 1

2. Using MySQL MIN() and MAX() with the WHERE Clause

Let us now use the MySQL MIN() and MAX() functions with the WHERE clause. How about finding the highest salary in the Operations department? We use the following query,

SELECT MAX(Salary) FROM Employee WHERE Department='Operations';

The WHERE clause filters through the table to extract only those rows with ‘Operations’ as the value in the Department column. Then, the MAX() function finds out the highest value in the Salary column among the rows extracted by the WHERE clause. We get the output as follows,

Max With Where

Now, how about finding the least salary in the Executive department? We use the below query,

SELECT MIN(Salary) FROM Employee WHERE Department='Executive';

And we get the output as,

Min With Where

3. Using MySQL MIN() and MAX() With the BETWEEN Clause

Let us now use the BETWEEN Operator with the MAX() function. What is the highest salary for all employees who joined the company between 1st January 2019 and 1st November 2020? We use the following query,

SELECT MAX(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01';

And we get the output as,

Max With Between

How about finding the lowest salary of employees who joined the company in the above duration. We use the following query,

SELECT MIN(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01';

The output is as follows,

Min With Between

4. Using MIN() and MAX() in Subqueries

We can use MAX() in MySQL subqueries. Let me demonstrate this with an example. Let us retrieve records of all the highest-paid employees in the company. We do so using:

SELECT * FROM Employee WHERE Salary=(SELECT MAX(Salary) FROM Employee);

The maximum value gets retrieved by the inner query. This is used in the condition of the WHERE clause and only those rows are returned which satisfy the condition.

We get the output as,

Max With Subqueries

Now, how about retrieving the record of those employees who have the least salary and have joined the company between 1st January 2019 and 1st November 2020. We do so using the query,

SELECT * FROM Employee WHERE Salary=(SELECT MIN(Salary) FROM Employee WHERE Date_Joined BETWEEN '2019-01-01' AND '2020-11-01');

And the output is as follows,

Min With Subqueries

Conclusion

Aggregate functions like MIN() and MAX() find a lot of use in data analysis. I would highly recommend you to check out the below reference links.


References