How to Use MIN and MAX Functions in SQL?

Welcome back to our SQL tutorial series! We’ve covered several essential SQL commands and operations so far. In this article, we’ll explore the MIN and MAX functions, which are used to find the minimum and maximum values in a column. These functions are crucial for data analysis and reporting, helping you quickly identify extreme values in your data sets.

Understanding the MIN and MAX Functions

The MIN function returns the smallest value in a specified column, while the MAX function returns the largest value. These functions are commonly used with numeric, date, and time data types but can also be used with text data to find the minimum and maximum values based on lexicographical order.

Basic Syntax of MIN and MAX

The basic syntax of the MIN and MAX functions is as follows:

SELECT MIN(column_name) FROM table_name;
SELECT MAX(column_name) FROM table_name;

Let’s look at some practical examples to understand how to use the MIN and MAX functions effectively.

Example Table: employees

We’ll continue to use the employees table for our examples:

Finding the Minimum Value

To find the minimum salary in the employees table, you use:

SELECT MIN(salary) AS min_salary FROM employees;

This command returns:

The minimum salary in the employees table is $70,000.

Finding the Maximum Value

To find the maximum salary in the employees table, you use:

SELECT MAX(salary) AS max_salary FROM employees;

This command returns:

The maximum salary in the employees table is $80,000.

Using MIN and MAX with WHERE Clause

You can use the MIN and MAX functions with the WHERE clause to filter the results based on specific conditions. For example, to find the minimum salary of Software Engineers:

SELECT MIN(salary) AS min_salary FROM employees WHERE position = 'Software Engineer';

This command returns:

The minimum salary among Software Engineers is $75,000.

Similarly, to find the maximum salary of Data Analysts:

SELECT MAX(salary) AS max_salary FROM employees WHERE position = 'Data Analyst';

This command returns:

The maximum salary among Data Analysts is $70,000.

Using MIN and MAX with GROUP BY

You can use the MIN and MAX functions with the GROUP BY clause to find the minimum and maximum values for each group. For example, to find the minimum and maximum salaries for each position:

SELECT position, MIN(salary) AS min_salary, MAX(salary) AS max_salary
FROM employees
GROUP BY position;

This command returns:

Using MIN and MAX with Other Aggregate Functions

You can combine the MIN and MAX functions with other aggregate functions like SUM, AVG, and COUNT to perform more comprehensive data analysis. For example, to find the minimum, maximum, and average salaries:

SELECT MIN(salary) AS min_salary, MAX(salary) AS max_salary, AVG(salary) AS avg_salary
FROM employees;

This command returns:

Conclusion

The MIN and MAX functions are powerful tools for identifying the smallest and largest values in your SQL database. Whether you’re looking for the lowest and highest salaries, the earliest and latest dates, or the minimum and maximum values in any column, these functions make it easy to perform these calculations.

In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!