OVER Clause in SQL Server(TSQL)

 

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.

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.

We can write a similar query with GROUP BY Clause as follows.

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *