The MIN() function in SQL is a powerful tool that allows us to determine the smallest or lowest value from a specified column or expression. It is widely used in data analysis to extract minimum values for decision-making, reporting, and business insights. This function automatically excludes NULL values during its computation, ensuring accurate results.
The MIN() function in SQL is a built-in aggregate function that returns the smallest value from a specified column or dataset. It is commonly used in queries to identify the minimum numeric, date, or string value within a table. The function automatically skips NULL values, ensuring precise results.
We can use it with various clauses like WHERE, GROUP BY, HAVING, or in subqueries to refine its output. The MIN() function is flexible and supports multiple use cases, from finding the lowest invoice amount to filtering groups based on conditions. It is a fundamental tool for data analysis and reporting in SQL.
Syntax:
SELECT MIN(column_name)
FROM table_name
[WHERE condition];
Key Terms:
- MIN(): Returns the smallest value in the column.
- NULL values: Ignored by the
MIN() function during computation.
- Aggregate functions: Functions like
MAX(), AVG(), and SUM() that perform operations on a set of values.
Examples of SQL MIN() Function
In this section, we will demonstrate the usage of the MIN() function with examples using a two sample tables. Employee Table, which stores details about employees, including their ID, name, city, designation, and associated PersonID. The Sales table records the month, price, product, and the corresponding employee responsible for the sale.
Employee Table:
Employee TableSales Table:
Sales TableExample 1: Find the Lowest Invoice Amount in a Month
To determine the smallest invoice price processed in a specific month. This query is particularly useful when analyzing monthly trends or identifying the least expensive transaction in a given period.
Query:
SELECT MIN(price) AS [Lowest Invoice]
FROM Sales
WHERE InvoiceMonth = 'July';
Output:
Explanation: The output displays the lowest price from the Sales table for invoices processed in July. The MIN(price) function evaluates all price entries within the filtered rows and returns the smallest value, which is $299.
Example 2: Find the Lowest Invoice in Specific Months
The IN clause allows for filtering data across multiple specified values, making it convenient to analyze trends or minimum values across a range of categories or time periods.
Query:
SELECT MIN(price) AS [Lowest Invoice]
FROM Sales
WHERE InvoiceMonth IN ('June', 'July');
Output
Explanation: The output shows the lowest invoice price from the Sales table for the months of June and July. The MIN(price) function evaluates all price entries for these months and identifies $99 as the smallest value, ensuring NULL values are excluded during the computation.
Using SQL MIN() Function with GROUP BY
The GROUP BY clause in SQL is used to organize rows into groups based on one or more columns. When combined with the MIN() function, it calculates the smallest value for each group. This is especially helpful when analyzing grouped data, such as finding the lowest transaction amounts for different categories, time periods, or regions.
Example 1: Minimum Invoice per Month
The GROUP BY clause is useful when we need to aggregate data into meaningful groups, such as months, categories, or regions, and perform calculations like finding the minimum value within each group.
Query:
SELECT InvoiceMonth, MIN(price) AS [Lowest Invoice]
FROM Sales
GROUP BY InvoiceMonth;
Output:
| InvoiceMonth | Lowest Invoice |
|---|
| June | $99 |
| July | $299 |
Explanation: The output displays the lowest invoice price for each month in the Sales table. The GROUP BY InvoiceMonth clause organizes the data by month, and the MIN(price) function calculates the smallest value for each group, resulting in $99 for June and $299 for July. This provides a clear comparison of minimum prices across months.
Example 2: Filtered Grouping
This query demonstrates how filtering and grouping can be combined to extract targeted insights. By using the WHERE clause, you limit the dataset to specific months, and the GROUP BY clause allows analysis within those filtered groups.
Query:
SELECT InvoiceMonth, MIN(price) AS [Lowest Invoice]
FROM Sales
WHERE InvoiceMonth IN ('June', 'July')
GROUP BY InvoiceMonth;
Output:
| InvoiceMonth | Lowest Invoice |
|---|
| June | $99 |
| July | $299 |
Explanation: The output provides the lowest invoice prices for June and July. The WHERE clause filters the data to include only the specified months, while the GROUP BY InvoiceMonth organizes the data into separate groups for June and July. The MIN(price) function then computes the smallest price within each group, returning $99 for June and $299 for July
Using SQL MIN() with ORDER BY
The ORDER BY clause is used to sort query results in ascending or descending order. When paired with the MIN() function, it helps prioritize groups based on their smallest values, making the output more organized and easier to interpret.
Example 1: Sort by Minimum Invoice Amount
To sort groups by their minimum invoice price in ascending order, the query uses the combination of GROUP BY and ORDER BY clauses:
Query:
SELECT InvoiceMonth, MIN(price) AS [Lowest Invoice]
FROM Sales
GROUP BY InvoiceMonth
ORDER BY MIN(price);
Output:
| InvoiceMonth | Lowest Invoice |
|---|
| June | $99 |
| July | $299 |
Explanation: The query groups the data by InvoiceMonth and calculates the lowest invoice price for each group using the MIN(price) function. The ORDER BY MIN(price) clause ensures that the results are displayed in ascending order of the minimum invoice amounts. As a result, June, with the lowest value of $99, appears before July, which has a minimum value of $299.
Using SQL MIN() with HAVING Clause
The HAVING clause is used to filter grouped data based on aggregate conditions. Unlike WHERE, which filters rows before grouping, HAVING applies conditions after the groups are formed. This method is effective for pinpointing groups that meet specific aggregate conditions.
Example 1: Minimum Invoice Greater Than $150
This query filters groups where the smallest invoice price exceeds $150.
Query:
SELECT InvoiceMonth, MIN(price) AS [Lowest Invoice]
FROM Sales
GROUP BY InvoiceMonth
HAVING MIN(price) > 150;
Output:
| InvoiceMonth | Lowest Invoice |
|---|
| July | $299 |
Explanation:
The GROUP BY clause groups data by InvoiceMonth, and the MIN(price) function calculates the smallest invoice price for each group. The HAVING clause filters out groups where the minimum price is $150 or less, leaving only July, where the lowest invoice is $299.
Example 7: Minimum Invoice Less Than $150
This query filters groups where the smallest invoice price is below $150.
Query:
SELECT InvoiceMonth, MIN(price) AS [Lowest Invoice]
FROM Sales
GROUP BY InvoiceMonth
HAVING MIN(price) < 150;
Output:
| InvoiceMonth | Lowest Invoice |
|---|
| June | $99 |
Explanation:
The GROUP BY clause groups data by InvoiceMonth, and the MIN(price) function computes the smallest invoice price for each group. The HAVING clause filters out groups where the minimum price is $150 or higher, leaving only June, where the lowest invoice is $99.
Combining MIN() with Other Aggregate Functions
The MIN() function can be combined with other aggregate functions such as MAX(), AVG(), and SUM() to generate a more comprehensive view of your data. This approach helps in summarizing key metrics in a single query.
Example 8: Aggregate Analysis
This query calculates the lowest, highest, average, and total invoice amounts for each month.
Query:
SELECT InvoiceMonth,
MIN(price) AS [Lowest Invoice],
MAX(price) AS [Highest Invoice],
AVG(price) AS [Average Invoice],
SUM(price) AS [Total Invoice]
FROM Sales
GROUP BY InvoiceMonth;
Output:
| InvoiceMonth | Lowest Invoice | Highest Invoice | Average Invoice | Total Invoice |
|---|
| June | $99 | $500 | $299.5 | $899 |
| July | $299 | $299 | $299.0 | $299 |
Explanation:
This query calculates multiple aggregate values for each month by grouping data with the GROUP BY clause. The MIN(price) retrieves the lowest invoice amount, MAX(price) provides the highest, AVG(price) calculates the average, and SUM(price) totals all invoice prices for each month. .
Using SQL MIN() IN Subqueries
Subqueries can be used with the MIN() function to fetch detailed information about the record that has the smallest value in a column. This technique is useful for isolating specific rows based on aggregate results.
Example 9: Fetch Record with Minimum Price
This query retrieves the complete record associated with the lowest invoice price.
Query:
SELECT *
FROM Sales
WHERE price = (SELECT MIN(price) FROM Sales);
Output:
| InvoiceMonth | Price | Product | PersonID |
|---|
| June | $99 | Laptop | 101 |
Explanation:
The subquery (SELECT MIN(price) FROM Sales) identifies the smallest price, $99, in the Sales table. The main query retrieves the complete record matching this price, providing detailed insights into the transaction with the lowest invoice amount.
Using SQL MIN() Within the WHERE Clause
The MIN() function can be directly used within the WHERE clause to filter rows based on the smallest value in a column. This approach allows precise targeting of records that match specific criteria.
Example 10: Details of Invoice with Minimum Price
This query retrieves detailed information about the invoice associated with the lowest price.
Query:
SELECT e.Name, e.City, e.Designation, s.InvoiceMonth, s.Price, s.Product
FROM Sales AS s
JOIN Employee AS e
ON e.ID = s.PersonID
WHERE s.Price = (SELECT MIN(price) FROM Sales);
Output:
| Name | City | Designation | InvoiceMonth | Price | Product |
|---|
| Alice | New York | Manager | June | $99 | Laptop |
Explanation:
The query joins the Employee and Sales tables to fetch detailed information about the invoice with the minimum price.
Conclusion
The SQL MIN() function is an essential aggregate function that simplifies data analysis by identifying the smallest value in a dataset. It can be used independently or combined with clauses like GROUP BY, ORDER BY, and HAVING to solve complex business requirements. From basic queries to advanced scenarios involving multiple aggregate functions, the MIN() function is a versatile tool in SQL. Use the examples and tips in this guide to apply the MIN() function effectively in our SQL projects.