SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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.

sample Sales Orders SQL database table model

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)
)
Code

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)
Code

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)
Code

SQL Min() and Max() function with Group By clause

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
Code

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.

SQL aggregation functions Min() and Max() with Partition By clause

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)
Code

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.

SQL aggregation function CTE query with Partition By clause

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
Code

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.

SQL Row_Number() function for Minimum and Maximum values

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.