Reader Level:
ARTICLE

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

Posted by Vipendra Verma Articles | SQL Server August 23, 2012
In this article I am going to explain how to use an over clause with Order By in SQL server 2012.
  • 0
  • 0
  • 4148

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

COMMENT USING

Trending up