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
(
invoiceidint notnull identityprimary key,
vendoridint notnull,
invoicenovarchar(15),
invoicetotalmoney,
paymenttotalmoney,
creadittotalmoney
)

Statement that inserts data into table:

insertinto mcninvoices values (20,'e001',100,100,0.00)
insertinto mcninvoices values (21,'e002',200,200,0.00)
insertinto mcninvoices values (22,'e003',500,0.00,100)
insertinto mcninvoices values (23,'e004',1000,100,100)
insertinto mcninvoices values (24,'e005',1200,200,500)
insertinto mcninvoices values (20,'e007',150,100,0.00)
insertinto mcninvoices values (21,'e008',800,200,0.00)
insertinto mcninvoices values (22,'e009',900,0.00,100)
insertinto mcninvoices values (23,'e010',6000,100,100)
insertinto 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
FROMdbo.mcninvoices

Output:

Penguins.jpg