How to Use the OVER Clause in SQL Server 2008 R2

This tutorial will show how to use the OVER clause in SQL Server 2008 R2. The OVER clause exposes a window of rows to certain kinds of calculations. The window of rows is simply a certain set of rows that the calculation operates on. Only Aggregate and ranking functions are the types of calculations that support the OVER clause. These functions are known as window functions because only a window of rows are exposed to these functions. The example used in this tutorial will use the OVER clause to calculate the total price of the unit prices and also the total price of each product Id.

Setting Up

In this tutorial we will be using a Product table in a Products database. Simple create a Products database and create a Product table in it. The Product table will have four columns: Prod_Id, ProductName, Unit, and UnitPrice.

PgSQL

1

2

3

4

5

6

7

8

9

USEProducts

CREATETABLEProduct

(

Prod_Idintnot null,

ProductNamenvarchar(30)not null,

Unitnvarchar(30)not null,

UnitPricefloatnot null

);

The USE statement specifies which database to use while the CREATE TABLE statement creates a table. Each column in the table uses a specific data type such as int, nvarchar, and float. The purpose of the data type is to let SQL Server know what kind of data to accept when values are being inserted into the table. Execute the query and it should complete successfully.

Now that we have a Product table created, we can insert values into it. To insert values into the table, we use the INSERT INTO, SELECT, and UNION statements. The INSERT INTO statement specifies which table and column to insert data into. The SELECT statement is the actual data being inserted into the table, and the UNION statement simply prevents duplicate values from being inserted into the table.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

USEProducts

INSERTINTOProduct(Prod_Id,ProductName,Unit,UnitPrice)

SELECT1,'Apples','1000 g',10.45

UNION

SELECT2,'Oranges','1500 g',21.75

UNION

SELECT3,'Strawberries','2000 g',15.30

UNION

SELECT4,'Bananas','500 g',32.56

UNION

SELECT5,'Grapes','750 g',5.15

UNION

SELECT1,'Green Apple','1000 g',7.45

UNION

SELECT1,'Pink Lady Apple','800 g',11.45

UNION

SELECT2,'Citrus','750 g',9.45

The OVER Clause

Now that we have a database and table in place, we can begin writing a query that takes advantage of the OVER clause. The goal of this query is to calculate the total of all the unit prices and the total unit price of the same product Id.

First we must specify that we are going to use the Products database. To do this we simply write “USE Products” at the top of the query to let SQL Server know “Hey, we are going to use a table in the Products database.” Next we use the SELECT statement to select the Prod_Id, ProductName, and UnitPrice columns. When writing the SELECT statement we must specify which table the columns are coming from by writing “Product.” before each column.

Next we will use the SUM function to calculate the total of the UnitPrice column with a non-partitioned OVER clause and place it in a column called “TotalUnitPrice.” Next we will again use the SUM function on the UnitPrice column but this time with a partitioned OVER clause, which restricts the rows. We will restrict the row by Prod_Id and place it in a column called “TotalProductId.” Last but not least we will use the FROM clause to specify Product as the table we are selecting from.

Output

Execute the query and you will see a table with columns of Prod_Id, ProductName, UnitPrice, TotalUnitPrice, and TotalProductId. TotalUnitPrice and TotalProductId are the columns we created in the query to place the values that use the OVER clause. As you can see, each row in the TotalUnitPrice column has the same value of 113.56. This is because we did not use the PARTITION BY clause to restrict the values to the current product Id. Now if you look in the TotalProductId where we did use the PARTITION BY clause, you can see that rows with the same Prod_Id have the same value.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.