SELECT SalesOrderID,p.Name AS ProductName,OrderQty,SUM(OrderQty) OVER(PARTITION BY SalesOrderID) AS TotalOrderQty,AVG(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Avg Qty of Item"
,COUNT(OrderQty)OVER(PARTITION BY SalesOrderID) AS "Total Number of Item"
,MIN(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Min order Qty"
,MAX(OrderQty) OVER(PARTITION BY SalesOrderID) AS "Max Order Qty" FROM Sales.SalesOrderDetail SOD INNER JOIN Production.Product p ON SOD.ProductID=p.ProductID WHERE SalesOrderID IN(43659,43664)
The Partition clause tell the aggregate function that the result should be based on the salesorderid. The output will looks like as given below
TotalOrderQty: is the total quantity of product ordered in the the sales order.
Avg Qty of Item : is the average of order quantity for a salesorder. In our case Totalorderqty for the salesorderid 43659 is 26 and we have twelve order line . So the average quantity per order line = 26/12
Total Number of Item : is the number of product ordered in a salesorder.
Min Order Qty : is the minimum quantity ordered in a salesorder.
SELECT
p.name,GRPRESULT.* FROM sales.SalesOrderDetail SOD INNER JOIN Production.Product p ON SOD.ProductID=p.ProductIDINNER JOIN ( SELECT
SalesOrderID
,SUM(OrderQty) AS TotalOrderQty
,AVG(OrderQty) AS "Avg Qty of Item"
,COUNT(OrderQty)AS "Total Number of Item" ,MIN(OrderQty) AS "Min order Qty" ,MAX(OrderQty) AS "Max Order Qty"
FROM Sales.SalesOrderDetail WHERE SalesOrderID IN(43659,43664)GROUP BY SalesOrderID)
GRPRESULT
ON
GRPRESULT .SalesOrderID =sod.SalesOrderID
Another interesting part is we can use the over clause with out partition clause which will do an aggregation on entire result set . Let us assume that we have requirement to list all sales order for the year 2008 with sales order number, total amount and Percentage of 2008 sales. It can be achieved easily as given below.
USE AdventureWorks2008
GOSELECT SalesOrderNumber,TotalDue,
(TotalDue*100.)/ SUM(TotalDue) OVER() AS [%2008Sales]FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate)=2008
In SQL server 2012 there are more options along with over clause to display cumulative total .
ROW_NUMBER, RANK, DENSE_RANK and NTILE are the ranking function which can be used along with Over clause. For ranking function along with Partition by clause, we can use Order by clause also.To explain the rank function let us have a small table
USE mydb
GO
CREATE TABLE Student
(
Name VARCHAR(10)
)
INSERT INTO Student VALUES ('aa'),('bb'),('cc'),('dd'),('ee')
INSERT INTO Student VALUES ('aa'),('bb'),('cc')
INSERT INTO Student VALUES ('aa'),('bb'),('cc')
INSERT INTO Student VALUES ('dd'),('ee')
INSERT INTO Student VALUES ('dd'),('ee')
INSERT INTO Student VALUES ('ff'),('gg'),('hh')
Row_Number() can be used in many scenarios like to filter the records, remove the duplicated records , implementing paging etc. Let us assume that we need to generate serial number while listing the entries from the student table.
SELECT ROW_NUMBER() OVER (ORDER BY NAME) AS [Si No],* FROM Student
To remove the duplicate entries from the above table
WITH cte_s
AS (
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY NAME) AS [SiNo],* FROM Student
)
DELETE FROM cte_s WHERE [SiNo]<>1
GO
SELECT * FROM Student
Let us assume that we have to divide the student into four group for a game. The NTILE will help us
SELECT NTILE(4) OVER (ORDER BY NAME) AS [Grpno],* FROM Student
As the total number of records 18 is not divisible by 4, it has created two groups with 5 students and other two groups with 4 students.
Let us have slightly different table structure to understand RANK and DENSE_RANK function.
CREATE TABLE StudentMark(
Name VARCHAR(10),
Mark INT)INSERT INTO StudentMark VALUES ('aa',10),('bb',14),('cc',16),('dd',22),('ee',25),('ff',25),('gg',11),('hh',21),('ii',16)To assign a rank to student based on their mark we can use the below query
SELECT RANK() OVER (ORDER BY mark DESC) AS 'Rank' ,* FROM StudentMark
The output will looks like as given below:
If you liked this post, do like my page on FaceBook
