OVER CLAUSE IN SQL SERVER

  • OVER() clause allows aggregation without requiring GROUP BY in the statement.
  • The OVER() clause can aggregate rows across groups of another field using the PARTION BY keyword.
  • PARTION BY or ORDER BY are not just limited to the SELECT list, they can use any fields made available by FROM clause.
  • Leaving OVER() blank causes it to apply the aggregation across all rows of the query.
  • ROW-RANGE are two new clauses added in 2012 version
  • UNBOUND, PRECEDING, FOLLOWING, CURRENT are 4 new keywords introduced in 2012 version
  • ORDER BY clause is must in OVER(), if you have used ROW or RANGE

Employee Name and total employees:

Name TotalEmp
Alex Adams 13
Barry Brown 13
Lee Osako 13
David Kennson 13
Eric Bender 13
Lisa Kendall 13
David Lonning 13
James Newton 13
Terry O’Haire 13
Sally Smith 13
Barbara O’Neil 13
Phil Wilconkinski 13
Janis Smith 13

Number of employees by Location: 

street city state TotalEmp
222 Second AVE Boston MA 3
444 Ruby ST Spokane WA 2
545 Pike Seattle WA 8

Let’s find the above same result in percentage:

street city state TotalEmp TotalEmpByLoc TotalEmpByLoc%
222 Second AVE Boston MA 13 3 23.07692308
444 Ruby ST Spokane WA 13 2 15.38461538
545 Pike Seattle WA 13 8 61.53846154

ROWS/RANGE UNBOUNDED PRECEDING:

rows-range

 ROWS/RANGE CURRENT ROW & UNBOUNDED FOLLOWING:

row-range-unbound

ROWS/RANGE PRECEDING & FOLLOWING without UNBOUNDED:

preceding-following

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “OVER CLAUSE IN SQL SERVER

  • June 7, 2016 at 9:23 pm
    Permalink

    Please post the lab insert script for this article. Thanks in advance.

    Reply
  • June 8, 2016 at 12:55 pm
    Permalink

    I just wanted to know the table column values for Employee and Location tables so that I can try to learn these new keywords introduced.

    Reply

Leave a Reply

%d bloggers like this: