SQL Min() and Max() Aggregation Functions with Partition By Clause
In this SQL tutorial for SQL Server database developers, I want to show how SQL Max() and Min() aggregate functions are used with Partition By clause in a Transact-SQL query. This additional syntax option provides analytical function properties to aggregation functions including Max() maximum and Min() minimum functions.
Even since SQL Server 2005, SQL programmers can use ranking functions or window functions (aka window table functions) like Row_Number(), Rank(), Dense_Rank() and NTile() functions, etc. Ranking functions can be extended and provide grouped data according to the Partition By clause.
The same scenario is valid for aggregate functions, too. For example Count() function.
In this tutorial, we will execute a SQL database query to output monthly minimum and maximum sales amounts of each customer.
Assume that in your SQL database, sales orders are stored in a single table according to the following simple database model. That is a basic database model since at least the amounts may be in different currencies and we should keep currency, exchange rates, conversion date and corresponding local currency amount fields, etc. Anyway, for simplicity and for the sake of this SQL tutorial this data model is enough.
Here is the CREATE TABLE command for our sample SQL database table
Create Table SalesOrders (
SalesOrderId int identity(1,1) not null primary key,
CustomerId int not null,
OrderDate date not null,
SalesPersonId int,
TotalAmount decimal(13,3)
)
And here is the SQL script that will generate sample data for our SQL database table
insert into SalesOrders values (101, '20180101', 11, 100)
insert into SalesOrders values (101, '20180102', 12, 220)
insert into SalesOrders values (300, '20180103', 10, 150)
insert into SalesOrders values (250, '20180103', 12, 110)
insert into SalesOrders values (101, '20180107', 10, 75)
insert into SalesOrders values (300, '20180109', 11, 275)
insert into SalesOrders values (250, '20180111', 10, 100)
insert into SalesOrders values (101, '20180115', 12, 200)
insert into SalesOrders values (300, '20180119', 11, 140)
insert into SalesOrders values (101, '20180126', 12, 180)
insert into SalesOrders values (250, '20180131', 11, 230)
insert into SalesOrders values (300, '20180203', 11, 225)
insert into SalesOrders values (101, '20180212', 12, 235)
insert into SalesOrders values (250, '20180222', 10, 245)
Basic usage of Min() and Max() SQL functions can provide each customers' monthly highest and lowest sales order amount values when used in combination with Group By clause as in below SQL query.
Select
CustomerId, Year(OrderDate) [Year], Month(OrderDate) [Month],
Min(TotalAmount) as Monthly_Minimum,
Max(TotalAmount) as Monthly_Maximum
From SalesOrders
Group By
CustomerId, Year(OrderDate), Month(OrderDate)
Although this SQL query gives database developer the enough information to have the maximum amount with Max() function and minimum amount with Min() function, SQL developer lost sales order number SalesOrderId and other order related data like SalesPersonId, etc.
Adding additional fields to the select field list will cause error since you will have to add those fields to the Group By clause. And this will change the data meanind retrieved from database table with SQL query.
Let's now change our first SQL Select statement as in following query using Partition By clause with Min() and Max() aggregate functions
Select
SalesOrderId, CustomerId, OrderDate, SalesPersonId, TotalAmount,
Min(TotalAmount) Over (Partition By CustomerId, Year(OrderDate), Month(OrderDate)) as Monthly_Minimum,
Max(TotalAmount) Over (Partition By CustomerId, Year(OrderDate), Month(OrderDate)) as Monthly_Maximum
From SalesOrders
Order By SalesOrderId
This SQL query lists all sales orders without filtering for rows for minimum and maximum values. On the other hand, the resultant data is enriched with new additional output columns from Sales Order table as well as two new calculated fields; one for monthly minimum amount and one for monthly maximum amount of the related customer.
Using a SQL CTE (Common Table Expression), database developers can get rid of the Sales Order table rows that we are not interested in as in following SQL query.
With CTE as (
Select
SalesOrderId, CustomerId, OrderDate, SalesPersonId, TotalAmount,
Min(TotalAmount) Over
(Partition By CustomerId, Year(OrderDate), Month(OrderDate)) as Monthly_Minimum,
Max(TotalAmount) Over
(Partition By CustomerId, Year(OrderDate), Month(OrderDate)) as Monthly_Maximum
From SalesOrders
)
Select
*,
Case
When TotalAmount > Monthly_Minimum Then 'Monthly Max Amount'
When TotalAmount < Monthly_Maximum Then 'Monthly Min Amount'
Else 'Single Order'
End as [Min or Max]
From CTE
Where (TotalAmount = Monthly_Minimum) or (TotalAmount = Monthly_Maximum)
Database developers can think of this usage of CTE expresion as a subquery, too. Here is the outcome of this database query execution on our sample table data.
Of course, if you are familier with SQL Row_Number() function with Partition By clause, we can build an alternative solution to this query requirement as follows.
Select
SalesOrderId, CustomerId, OrderDate, SalesPersonId, TotalAmount,
Row_Number() Over (Partition By CustomerId, Year(OrderDate), Month(OrderDate) Order By TotalAmount Asc) as Monthly_Minimum,
Row_Number() Over (Partition By CustomerId, Year(OrderDate), Month(OrderDate) Order By TotalAmount Desc) as Monthly_Maximum
From SalesOrders
Above SQL query will give the database developer the sort order of that sales order according to to minimum and maximum values for that customer in the order's month.
These sort orders can be used in a CTE expression to filter only the minimum and maximum figures for each customers in each month. One last note though this tutorial is based on sample database table and SQL scripts created on SQL Server data platform, for other database platforms like SQLScript and HANA, or PL/SQL and Oracle, the same results are valid because this usage is a standard SQL feature.