Today, I will explain an overview of the SQL MIN () function along with its several use cases in this article. This function is categorized under aggregate functions in SQL Server. Aggregate functions perform a calculation on a set of values from a specified expression and return a single value in their output. Aggregate functions return the same value every time you execute them unless your source data is changed.
There are other functions as well in T-SQL that is categorized under aggregate functions. The list of such functions is:
- AVG
- COUNT
- COUNT_BIG
- MAX
- MIN
- SUM
- VAR
- STRING_AGG
- GROUPING
- GROUPING_ID
- APPROX_COUNT_DISTINCT
- CHECKSUM_AGG
As I explained above, today we are going to understand about SQL MIN () statement so this article will be focused on this function only going forward.
SQL MIN () function returns minimum or lowest value from specified expression. It ignores NULL values from columns. I will show you its use cases to make you understand it from different aspects with the help of examples given in this article. The syntax of this SQL function is given below.
|
1 2 |
SELECT MIN (column) FROM table |
Let me explain my source tables on which I will showcase all use cases of SQL MIN function given in this article.
I have two tables “Employee” and “Sales”. Table “Employee” stores all the details regarding employees and the “Sales” table stores sales and invoice-related details. I will use these two tables in the below use cases for this demonstration. If you don’t have any table to test these use cases, then you can create them with few sets of values. The queries given in this article will return output as per data stored in these tables.
Use cases of SQL MIN statement
This section will describe various use cases of MIN statement starting from its basic use to its uses with GROUP BY, ORDER BY, HAVING, or other clauses. I have given an example to demonstrate the use cases in a flow. You can use all these use cases as per your requirement.
Simple use case
Let’s start with its simple use case in which I will retrieve the minimum or lowest invoice price processed in a specific month. Here, I will show you this example of using the MIN function using a SELECT statement.
Suppose you want to know the minimum or lowest invoice amount processed in July 2021 or any specific month. Price column stores each invoice cost that is processing against each sale.
|
1 2 3 4 |
SELECT MIN (price) AS [Lowest Invoice] FROM Sales WHERE InvoiceMonth = ‘July’ GO |
Here is its output which is showing that the price of the lowest invoice processed in July was $299.
Let’s change this example as per the different requirements. If you want to retrieve the lowest or minimum value from the specified set of inputs, then this can also be achieved using this function.
Let’s assume the above example in which we have retrieved the lowest Invoice from a specific month July. Now, let’s consider there is another requirement that came in which you need to pull out the lowest invoice cost from a specific quarter or some specific months. We can use SQL IN statement with WHERE clause to list all months from which we must calculate the lowest value.
Let’s fit this requirement into our example. I want to get the lowest invoice from months June and July, so I used the above SQL query with a few modifications to include both months with the help of the SQL IN statement as shown in the below statements.
|
1 2 3 4 |
SELECT MIN (price) AS [Lowest Invoice] FROM Sales WHERE InvoiceMonth IN (‘June’, ‘July’) GO |
We can see its output in the below image where the lowest invoice is now showing as $99 whereas it was $299 in July 2021 in the above example.
I hope you understand how to use this function. You can get the lowest or minimum value using this function from any column, even you can filter the lowest or minimum value between specified expressions using the WHERE clause. The next section will explain its uses with the GROUP BY statement.
Use SQL MIN function with GROUP BY statement
This function is often used with the GROUP BY statement to group the output based on the specified expression. There are various use scenarios where the GROUP BY statement is helpful. Let me show you one requirement where you want to list the lowest invoice from each month. You can get it done easily using this function by applying the GROUP BY statement on the month’s column.
|
1 2 3 4 |
SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice] FROM Sales GROUP BY InvoiceMonth GO |
This example will group all invoices on monthly basis and return the lowest invoice price from each month as shown in the below image.
I have executed the above query on the whole table without putting any filter. The result is showing output from each month’s data stored in that table.
You can also get a list of the lowest invoice per month from specified months. Run the below statement to get their lowest invoice pricing from the specified months of July and June.
|
1 2 3 4 5 |
SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice] FROM Sales WHERE InvoiceMonth IN (‘June’, ‘July’) GROUP BY InvoiceMonth GO |
The below output is showing details from only specified two months “June” and “July”. You can filter the above result based on any number of specified inputs like we have specified for only 2 months here to show you this demonstration.
USE SQL MIN function with ORDER BY statement
We can also use the SQL MIN function with the ORDER BY statement. We can use the ORDER BY statement to sort the output that will be returned using the GROUP BY statement. Just have a look at the output given in the above examples of GROUP BY statement. We will add another SQL statement ORDER BY in the above query to get its output.
|
1 2 3 4 5 |
SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice] FROM Sales GROUP BY InvoiceMonth ORDER BY MIN(price) GO |
The output of the above query is the same as it is showing for the GROUP BY statement, both have 4 rows. The only difference between both outputs is ORDER BY statement has sorted the output in ascending order whereas the GROUP BY statement has output results in random order.
Use SQL MIN function with HAVING statement
Let’s see another aspect of the above query or example, this will be an extension of the above query by adding a line of code for the HAVING clause. Consider, you want to find all months in which your minimum invoice cost was not processed with less than $150. In other words, we can say that all invoices processed in that month are more than $150 and not even a single invoice is priced at less than $150.
The below query will return all months in which even the minimum invoice remains higher than $150 in that month.
|
1 2 3 4 5 |
SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice] FROM Sales GROUP BY InvoiceMonth HAVING MIN(price)>150 GO |
July is the month in which we have processed all invoices greater than $150 and the lowest invoice processed in that month was at $299.
Let’s reverse this requirement if you want to get the list of all months in which your minimum invoice was processed was less than $150 and not greater than $150. In other words, the below query will return all months in which we have invoiced at least one sale with less than $150 and exclude all months where the lowest invoice price is greater than $150.
|
1 2 3 4 5 |
SELECT InvoiceMonth, MIN (price) AS [Lowest Invoice] FROM Sales GROUP BY InvoiceMonth HAVING MIN(price)<150 GO |
The output of the above query is showing the result in the below image where we can see there are no details for July month.
USE SQL MIN function with other aggregate functions like MAX, AVG, SUM
We can also use the SQL MIN function along with other aggregate functions like MAX, AVG, SUM, etc.
- MAX – This function will return the highest or maximum value from the specified column or expression
- AVG – This will return the average value of a specified column or expression
- SUM – This function will return the sum of all values of a specified column or expression
Below is the example where I have used other aggregate functions to get the lowest, highest, average, and total invoice cost for each month.
|
1 2 3 4 5 6 7 8 |
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 GO |
Output has returned in the below image. You can analyze its output to understand more about these aggregate functions. We can see the lowest value in column lowest Invoice using SQL MIN function, highest cost using MAX function average, and the sum of all values in their respective columns of below image.
You can use the HAVING clause as well along with all these aggregate functions.
|
1 2 3 4 5 6 7 8 9 |
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 HAVING MIN(price)>150 GO |
The below example is showing the lowest, highest, average, and total invoice cost of any month in which there was not any invoice processed of less than $150. It means all invoices processed in July were more than $150.
USE MIN function within WHERE Clause
There are many examples I have given above using which we can learn how to use the SQL MIN function with the WHERE clause, but this section will explain how to use the MIN statement within the WHERE clause. The below query will list details about the invoice which was processed with minimum or lowest price.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT name, City, Designation, InvoiceMonth, Price, Product FROM Sales AS s JOIN Employee AS e ON e.ID=s.PersonID WHERE price = (select MIN(price) from sales) GO |
Output is showing invoice details like name, city, product, and months along with its lowest invoice cost.
The above query will return all invoices if there is more than one invoice processed with the same amount. Let me show you this by adding another entry with the same invoice price. I have added another row for May with the same price of $99 to understand whether we will get all results with the same lowest price or not. We can see now, both invoice details are showing in their output.
Conclusion
I have explained an overview and various use cases of the SQL MIN function statement. This function is used to return the minimum or lowest value from the specified column in that function. You can learn its uses by looking into each example given in the above sections.
Please let us know about your feedback in the comment section.
He loves to talk and write about database technologies. He has lead and delivered many projects from designing to deployments on Migrations to the cloud, heterogeneous migrations, Database consolidations, upgrades, heterogeneous replication, HA / DR solutions, automation, and major performance tuning projects.
You can also find him on LinkedIn
View all posts by Manvendra Singh
- Configure backup storage redundancy for Azure Cosmos DB account - May 15, 2024
- Difference between SQL SELECT UNIQUE and SELECT DISTINCT - January 16, 2024
- How to do a group by clause - April 12, 2023