Use of OVER CLAUSE with Aggregate function to make Analytic Function in Sql Server

Introduction

Analytic function is a function which behaves very similar to an aggregate function with the exception that for each aggregated set, aggregate function returns one last but an analytic function returns intermediate results also.

It is done by adding the OVER keyword along with PARTITION BY and ORDER BY. the PARTITION BY clause is use for grouping the records while the presence of ORDER BY ensures the sorted order of occurrence of the intermediate rows.

In this article, we will look into an example of the use of OVER CLAUSE with Aggregate function to make Analytic Function

Let us consider a scenario where "Agents" has been asked to collect money and the report should contain the "Total Amount Collected" by the "Agent" with the "Highest" and "Lowest" amount received.

Let us look into what the problem is stating - say we have the Raw input data generated by using the following SQL script

Declare @T Table(AgentName Varchar(20),CollectedAmount Decimal)
Insert Into @T Values
('AgentName1',123.56)
,('AgentName1',153.56)
,('AgentName1',223.56)
,('AgentName1',3.46)
,('AgentName1',1256.96)
,('AgentName1',556.10)
,('AgentName1',534.56)
,('AgentName1',999.78)
,('AgentName1',638.00)
,('AgentName2',789.00)
,('AgentName2',666.56)
,('AgentName2',88.45)
,('AgentName2',663.46)
,('AgentName2',256.00)
,('AgentName2',456.67)

SELECT 	*
FROM @T

The final output should appear as

Solution

We propose the below solution for the aforementioned problem

SELECT 
		AgentName
		,[Total Amount Collected]
		,[Highest Amount Collected]
		,[Lowest Amount Collected]
FROM(
		SELECT 
			*
			,Rn=ROW_NUMBER() OVER(PARTITION BY AgentName ORDER BY CollectedAmount DESC)
			,[Total Amount Collected] = SUM(CollectedAmount) OVER(PARTITION BY AgentName ORDER BY (SELECT 1))
			,[Highest Amount Collected] = MAX(CollectedAmount) OVER(PARTITION BY AgentName ORDER BY (SELECT 1))
			,[Lowest Amount Collected] = MIN(CollectedAmount) OVER(PARTITION BY AgentName ORDER BY (SELECT 1))
		FROM @T
	)X
Where X.Rn=1

Let us try to understand what the inner query

SELECT 
			*
			,Rn=ROW_NUMBER() OVER(PARTITION BY AgentName ORDER BY CollectedAmount DESC)
			,[Total Amount Collected] = SUM(CollectedAmount) OVER(PARTITION BY AgentName ORDER BY (SELECT 1))
			,[Highest Amount Collected] = MAX(CollectedAmount) OVER(PARTITION BY AgentName ORDER BY (SELECT 1))
			,[Lowest Amount Collected] = MIN(CollectedAmount) OVER(PARTITION BY AgentName ORDER BY (SELECT 1))
		FROM @T

is doing.

Upon executing the inner query, we receive

The ROW_NUMBER() function provides the ranking. The records are PARTITION BY "AgentName" and the ORDER BY ensures the ordering of records within the partition by "CollectedAmount" in descending order.

The SUM function is basically an aggregated function. But with the presence of OVER Clause it acts as Analytic Function. In this case, we are performing a summation of "CollectedAmount" PARTITION BY "AgentName". The sorting is performed by using ORDER BY over "SELECT 1". The presence of "SELECT 1" will return the same number of rows. We can use any other value like "SELECT NULL", "SELECT GETDATE()", "SELECT 2" etc. to get the same result. It's a kind of dummy column which guarantees that the record ordering will not change which would otherwise change had it been done on some existing column.

The same applies for MAX and MIN function.

Now our objective is to pick up those records where Rn=1 which we have done in the outer query

SELECT 
		AgentName
		,[Total Amount Collected]
		,[Highest Amount Collected]
		,[Lowest Amount Collected]
FROM(
		SELECT 
			*
			.................
			.................
			.................
		FROM @T
	)X
Where X.Rn=1 -> Ensures we receive the latest record of the "CollectedAmount" for every Agent.

Conclusion

This article taught us how use OVER CLAUSE with Aggregate function to make Analytic Function. Hope this will be helpful. Thanks for reading. Zipped file is attached herewith.