How to Use Over Clause With Order By in SQL Server 2012

Introduction

In this article I am going to explain how to use an Over clause with Order By in SQL server 2012. An Over clause is used to determine the partitioning and ordering of a rowset before the associated window function is applied.

The OVER clause in SQL server 2012 works with functions to compute aggregated values such as cumulative aggregates and  moving averages.

Here we use an OVER clause to determine the cumulative amount of an invoicetotal. We use an OVER clause with Group By to determine the cumulative amount of an invoicetotal.

Statement that creates a table:

createtable mcninvoices
(

invoiceid
int notnull identityprimary key,
vendorid
int notnull,
invoiceno
varchar(15),
invoicetotal
money,
paymenttotal
money,
creadittotal
money

)

 Statement that inserts data into table:

insertinto mcninvoices values (20,'e001',100,100,0.00)
insert
into mcninvoices values (21,'e002',200,200,0.00)
insert
into mcninvoices values (22,'e003',500,0.00,100)
insert
into mcninvoices values (23,'e004',1000,100,100)
insert
into mcninvoices values (24,'e005',1200,200,500)
insert
into mcninvoices values (20,'e007',150,100,0.00)
insert
into mcninvoices values (21,'e008',800,200,0.00)
insert
into mcninvoices values (22,'e009',900,0.00,100)
insert
into mcninvoices values (23,'e010',6000,100,100)
insert
into mcninvoices values (24,'e011',8200,200,500)

 Statement that shows all the data of mcninvoicetable:

Clipboard04.jpg

 Statement that is used to determine the cumulative amount of an invoicetotal:
 

SELECTinvoiceid,vendorid,invoicetotal,
SUM
(invoicetotal)OVER (PARTITIONBY vendorid
ORDER
BY [invoiceid])AS Cumulativeinvoices 
FROM
dbo.mcninvoices

Output:

Penguins.jpg

Up Next
    Ebook Download
    View all
    Learn
    View all