We send a dataset to a function by grouping it with the Partition By statement int the OVER Clause.
We sort the groups by a specific column with the ORDER By statement in the OVER Clause.
Let’s make an example for a better understanding of OVER Clause.
Example:
First, we create a table with the help of the following script and add a few records into this table.
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [TestDB] GO CREATE TABLE [dbo].[ProductTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [ProductCategory] [varchar](250) NOT NULL, [ProductName] [varchar](250) NOT NULL, [ProductPrice] int ) ON [PRIMARY] GO INSERT INTO [dbo].[ProductTable] VALUES ('technology','Samsung NOTE 8',3400), ('kitchenstuff','mixer',430),('technology','LG Television',6700),('kitchenstuff','bakery',9800) |
Then use OVER Clause to group by ProductCategory and use the AVG function to calculate the average price of each group and use the COUNT function to calculate the number of partitions in each group. I’m preventing recurring records with DISTINCT Clause.
|
1 2 3 4 |
Select DISTINCT ProductCategory, AVG(ProductPrice) OVER(PARTITION BY ProductCategory ORDER BY ProductCategory DESC) AS 'Average_Product_Price_Per_Group' , Count(*) OVER(PARTITION BY ProductCategory ORDER BY ProductCategory DESC) AS 'Member_Count_in_Partition' From [dbo].[ProductTable] |
We can write a similar query with GROUP BY Clause as follows.
|
1 2 3 4 |
Select ProductCategory, AVG(ProductPrice) Average_Product_Price_Per_Group,Count(*) AS 'Member_Count_in_Partition' From [dbo].[ProductTable] GROUP BY ProductCategory |
Of course there are some differences between GROUP BY Clasue and OVER Clause.
The differences between Partition By in OVER Clause and GROUP BY Clause:
| Group By affects the entire query | OVER Clause affects only the corresponding function |
| The number of rows changes when you use Group By | The number of rows does not change when you use OVER Clause. (You will see this when you remove DISTINCT Clause from the above query) |
| If you want to add a column to a Select Statement when you use Group By, that column must be either in the GROUP BY statement or in an AGGREGATE Function. | There is no such limitation in OVER Clause. Because OVER Clause doesn’t affect the entire query. It only affects the corresponding function. You can see this usage in the following example. |
|
1 2 3 4 |
Select ProductCategory,ProductName, AVG(ProductPrice) OVER(PARTITION BY ProductCategory ORDER BY ProductCategory DESC) AS 'Average_Product_Price_Per_Group' , Count(*) OVER(PARTITION BY ProductCategory ORDER BY ProductCategory DESC) AS 'Member_Count_in_Partition' From [dbo].[ProductTable] |
