MySQL MIN() function

Last update on May 01 2024 12:09:20 (UTC/GMT +8 hours)

MIN() function

MySQL MIN() function returns the minimum value of an expression. MIN() function returns NULL when the return set has no rows. It's a fundamental aggregate function that provides valuable insights into data analysis.

This function is useful in -

This is essential for understanding the lower limit of a dataset.
In business analysis, the MIN() function helps evaluate the lowest performance metrics, such as minimum expenses, losses, or user engagement.
The MIN() function can be used to identify the trough points or trends in a time series dataset, helping to spot periods of lowest activity.
When comparing performance against benchmarks, the MIN() function assists in determining how close or far data is from the least achieved results.
For critical decisions, knowing the minimum value in relevant data attributes can provide insights for strategic planning.
Monitoring data for exceptional cases, such as dropping below a certain threshold or minimum value, can trigger alerts for timely actions.

Syntax:

MIN(expr);

Where expr is an expression.

MySQL Version: 8.0

Contents:

Example : MySQL MIN() function
MySQL MIN() function with group by and example
MySQL MIN() function with group by, order by and example
MySQL MIN() function with having and example
MySQL MIN() function with distinct and example

Example : MySQL MIN() function

The following statement will return the minimum 'book_price' from 'book_mast' table.

Sample table: book_mast

Code:


-- This SQL query retrieves the minimum book price from the book_mast table.
SELECT MIN(book_price) -- Selects the minimum value of the book price
FROM book_mast; -- Specifies the table from which to retrieve data (book_mast table)

Explanation:

This SQL query retrieves data from the book_mast table.

It selects the minimum value of the book price.

Here's how the process works:

The query uses the MIN() function to find the minimum value of the book price column.

It retrieves this value from the book_mast table.

Finally, the query returns the minimum value of the book price.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

mysql> SELECT MIN(book_price)
    -> FROM book_mast;
+-----------------+
| MIN(book_price) |
+-----------------+
|           45.00 | 
+-----------------+
1 row in set (0.00 sec)

Example: MySQL MIN() function with group by

MySQL MIN() function retrieves the minimum value of an expression which has undergone a grouping operation, if used with GROUP BY clause. The following MySQL statement will extract all "cate_id"s and the minimum 'book_price' for each group of 'cate_id'. GROUP BY clause has grouped "cate_id"s.

Sample table: book_mast

Code:


-- This query selects the category ID and the minimum book price for each category from the table 'book_mast'
SELECT cate_id, MIN(book_price)
-- This statement specifies the columns to be selected: 'cate_id' and the minimum value of 'book_price'
FROM book_mast           
-- This part of the query specifies the table from which data is being retrieved, which is 'book_mast'
GROUP BY cate_id;
-- This clause groups the results by the 'cate_id' column, so that the minimum book price can be calculated for each category separately

Explanation:

The purpose of this SQL query is to find the minimum price of books for each category in the book_mast table.

SELECT cate_id, MIN(book_price): This part of the query selects two columns: cate_id and the minimum value of book_price.

FROM book_mast: This part specifies the table from which the data is being selected, which is the book_mast table.

GROUP BY cate_id: This clause groups the results by the cate_id column. It ensures that the minimum book price is calculated for each unique category separately.

The query will return a list of category IDs along with the minimum price of books within each category. If there are multiple books belonging to the same category, the query will find the minimum price among them and display it alongside the corresponding category ID.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

mysql> SELECT cate_id, MIN(book_price)
    -> FROM book_mast
    -> GROUP BY cate_id;
+---------+-----------------+
| cate_id | MIN(book_price) |
+---------+-----------------+
| CA001   |           85.00 | 
| CA002   |          105.50 | 
| CA003   |           45.00 | 
| CA004   |           88.00 | 
| CA005   |           50.50 | 
+---------+-----------------+
5 rows in set (0.00 sec)

Pictorial Presentation:

Example: MySQL MIN() function with group by and order by

The following MySQL statement will extract those countries ('country') and publisher cities ('pub_city') which have the minimum number of branches ('no_of_branch') for each group of 'country' and 'pub_city'. 'GROUP BY' clause have grouped 'country' and 'pub_city' and the 'country' column have been sorted in ascending order by the usage of ORDER BY clause.

Sample table: publisher

Code:


-- This query selects the country, publisher city, and the minimum number of branches for each combination of country and publisher city from the table 'publisher'
SELECT country, pub_city, MIN(no_of_branch)
-- This statement specifies the columns to be selected: 'country', 'pub_city', and the minimum value of 'no_of_branch'
FROM publisher 
-- This part of the query specifies the table from which data is being retrieved, which is 'publisher'
GROUP BY country, pub_city 
-- This clause groups the results by the combination of 'country' and 'pub_city', so that the minimum number of branches can be calculated for each combination

Explanation:

The purpose of this SQL query is to find the minimum number of branches for each combination of country and publisher city in the publisher table.

SELECT country, pub_city, MIN(no_of_branch): This part of the query selects three columns: country, pub_city, and the minimum value of no_of_branch.

FROM publisher: This part specifies the table from which the data is being selected, which is the publisher table.

GROUP BY country, pub_city: This clause groups the results by the combination of country and pub_city. It ensures that the minimum number of branches is calculated for each unique combination of country and publisher city separately.

The query will return a list of countries and publisher cities along with the minimum number of branches within each combination. If there are multiple publishers in the same city within the same country, the query will find the minimum number of branches among them and display it alongside the corresponding country and publisher city.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

mysql> SELECT country,pub_city,MIN(no_of_branch)
    -> FROM publisher
    -> GROUP BY country,pub_city
    -> ORDER BY country;
+-----------+-----------+-------------------+
| country   | pub_city  | MIN(no_of_branch) |
+-----------+-----------+-------------------+
| Australia | Adelaide  |                 6 | 
| India     | Mumbai    |                10 | 
| India     | New Delhi |                10 | 
| UK        | Cambridge |                 6 | 
| UK        | London    |                 8 | 
| USA       | Houstan   |                25 | 
| USA       | New York  |                10 | 
+-----------+-----------+-------------------+
7 rows in set (0.00 sec)

Example: MySQL MIN() function with having

MySQL MIN() function retrieves the minimum value from an expression which has undergone a grouping operation by GROUP BY clause and filtered using HAVING clause followed by some condition. The following MySQL statement will extract those countries ('country') which have ten or less number of branches.

Sample table: publisher

Code:


-- This query selects the country and the minimum number of branches for each country from the table 'publisher', 
-- but only for countries where the minimum number of branches is less than 10.
SELECT country, MIN(no_of_branch) 
-- This statement specifies the columns to be selected: 'country' and the minimum value of 'no_of_branch'
FROM publisher          
-- This part of the query specifies the table from which data is being retrieved, which is 'publisher'
GROUP BY country 
-- This clause groups the results by the 'country' column, so that the minimum number of branches can be calculated for each country separately
HAVING MIN(no_of_branch) < 10;
-- This clause filters the grouped results based on a condition, where the minimum number of branches for each country must be less than 10

Explanation:

The purpose of this SQL query is to find countries where the minimum number of branches for any publisher within that country is less than 10.

SELECT country, MIN(no_of_branch): This part of the query selects two columns: country and the minimum value of no_of_branch.

FROM publisher: This part specifies the table from which the data is being selected, which is the publisher table.

GROUP BY country: This clause groups the results by the country column. It ensures that the minimum number of branches is calculated for each unique country separately.

HAVING MIN(no_of_branch) < 10: This clause filters the grouped results based on a condition, where the minimum number of branches for each country must be less than 10. It filters out countries where the minimum number of branches is not less than 10.

The query will return a list of countries along with the minimum number of branches within each country, but only for countries where the minimum number of branches is less than 10.

Relational Algebra Expression:

Relational Algebra Tree:

Output:

mysql> SELECT country,MIN(no_of_branch)
    -> FROM publisher
    -> GROUP BY country
    -> HAVING MIN(no_of_branch)<10;
+-----------+-------------------+
| country   | MIN(no_of_branch) |
+-----------+-------------------+
| Australia |                 6 | 
| UK        |                 6 | 
+-----------+-------------------+
2 rows in set (0.00 sec)

Example: MySQL MIN() function with distinct

MySQL MIN() function retrieves the unique minimum value of an expression if the function is accompanied by a DISTINCT clause. The following statement will extract category 'cat_id' wise minimum number of page 'no_page' from the 'book_mast' table.

Example:

Sample table: book_mast

Code:


-- This query selects the category ID and checks if the minimum value of distinct 'no_page' for each category is greater than 0 from the table 'book_mast'.
SELECT cate_id, MIN(DISTINCT no_page) > 0              
-- This statement specifies the columns to be selected: 'cate_id', and a conditional expression checking if the minimum distinct value of 'no_page' is greater than 0.
FROM book_mast          
-- This part of the query specifies the table from which data is being retrieved, which is 'book_mast'.
GROUP BY cate_id;
-- This clause groups the results by the 'cate_id' column, so that the minimum distinct value of 'no_page' can be calculated for each category separately.

Explanation:

The purpose of this SQL query is to determine for each category whether the minimum number of pages for any book in that category is greater than 0.

SELECT cate_id, MIN(DISTINCT no_page) > 0: This part of the query selects two columns: cate_id, and a conditional expression that checks if the minimum distinct value of no_page is greater than 0. The result will be either 1 (true) or 0 (false).

FROM book_mast: This part specifies the table from which the data is being selected, which is the book_mast table.

GROUP BY cate_id: This clause groups the results by the cate_id column. It ensures that the minimum distinct value of no_page is calculated for each unique category separately.

The query will return a list of category IDs along with a boolean value indicating whether the minimum number of pages for any book in each category is greater than 0. If the value is 1, it means there exists at least one book with more than 0 pages in that category, otherwise, it means all books in that category have 0 pages or null values.

Output:

mysql> SELECT cate_id, MIN(DISTINCT no_page)
    -> FROM book_mast
    -> GROUP BY	cate_id;
+---------+-----------------------+
| cate_id | MIN(DISTINCT no_page) |
+---------+-----------------------+
| CA001   |                   201 | 
| CA002   |                   300 | 
| CA003   |                    95 | 
| CA004   |                   165 | 
| CA005   |                    88 | 
+---------+-----------------------+
5 rows in set (0.00 sec)

Previous: Max() with having
Next: STD()