over partition by in sql server 2008
sql server over clause partition
partition by clause in sql server 2008
over partition by clause in sql
In this video we will discuss the power and use of Over clause in SQL Server.
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.
[ Ссылка ]
The OVER clause combined with PARTITION BY is used to break up data into partitions.
Syntax : function (...) OVER (PARTITION BY col1, Col2, ...)
The specified function operates for each partition.
For example :
COUNT(Gender) OVER (PARTITION BY Gender) will partition the data by GENDER i.e there will 2 partitions (Male and Female) and then the COUNT() function is applied over each partition.
Any of the following functions can be used. Please note this is not the complete list.
COUNT(), AVG(), SUM(), MIN(), MAX(), ROW_NUMBER(), RANK(), DENSE_RANK() etc.
Example :
SQl Script to create Employees table
Create Table Employees
(
Id int primary key,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
Go
Insert Into Employees Values (1, 'Mark', 'Male', 5000)
Insert Into Employees Values (2, 'John', 'Male', 4500)
Insert Into Employees Values (3, 'Pam', 'Female', 5500)
Insert Into Employees Values (4, 'Sara', 'Female', 4000)
Insert Into Employees Values (5, 'Todd', 'Male', 3500)
Insert Into Employees Values (6, 'Mary', 'Female', 5000)
Insert Into Employees Values (7, 'Ben', 'Male', 6500)
Insert Into Employees Values (8, 'Jodi', 'Female', 7000)
Insert Into Employees Values (9, 'Tom', 'Male', 5500)
Insert Into Employees Values (10, 'Ron', 'Male', 5000)
Go
Write a query to retrieve total count of employees by Gender. Also in the result we want Average, Minimum and Maximum salary by Gender.
This can be very easily achieved using a simple GROUP BY query as show below.
SELECT Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal,
MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal
FROM Employees
GROUP BY Gender
What if we want non-aggregated values (like employee Name and Salary) in result set along with aggregated values
You cannot include non-aggregated columns in the GROUP BY query.
SELECT Name, Salary, Gender, COUNT(*) AS GenderTotal, AVG(Salary) AS AvgSal,
MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal
FROM Employees
GROUP BY Gender
The above query will result in the following error : Column 'Employees.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
One way to achieve this is by including the aggregations in a subquery and then JOINING it with the main query as shown in the example below. Look at the amount of T-SQL code we have to write.
SELECT Name, Salary, Employees.Gender, Genders.GenderTotals,
Genders.AvgSal, Genders.MinSal, Genders.MaxSal
FROM Employees
INNER JOIN
(SELECT Gender, COUNT(*) AS GenderTotals,
AVG(Salary) AS AvgSal,
MIN(Salary) AS MinSal, MAX(Salary) AS MaxSal
FROM Employees
GROUP BY Gender) AS Genders
ON Genders.Gender = Employees.Gender
Better way of doing this is by using the OVER clause combined with PARTITION BY
SELECT Name, Salary, Gender,
COUNT(Gender) OVER(PARTITION BY Gender) AS GenderTotals,
AVG(Salary) OVER(PARTITION BY Gender) AS AvgSal,
MIN(Salary) OVER(PARTITION BY Gender) AS MinSal,
MAX(Salary) OVER(PARTITION BY Gender) AS MaxSal
FROM Employees
Text version of the video
[ Ссылка ]
Slides
[ Ссылка ]
All SQL Server Text Articles
[ Ссылка ]
All SQL Server Slides
[ Ссылка ]
All Dot Net and SQL Server Tutorials in English
[ Ссылка ]
All Dot Net and SQL Server Tutorials in Arabic
[ Ссылка ]
