SQL Server 2005 OVER Clause

Home > Blogs > SQL Server > SQL Server 2005 OVER Clause

SQL Server 2005 OVER Clause

Like This Blog 0 Jeff Jones
Added by September 15, 2006

One of the features of SQL Server 2005 that does not get a lot of press is the new window-based calculation clause called OVER. You can now generate different aggregates with different grouping columns in the same query, without having to use subqueries. This is especially useful when you need to include aggregated values along side detail data in the same result set or compute percentages based on different groupings.

The OVER clause is mostly used when performing the new RANK, DENSE_RANK, ROW_NUMBER and NTILE functions. But it can be used with standard scalar aggregates like SUM, AVG, etc..

The following is an example of using the OVER clause with the PARTITION BY to generate a couple of different aggregates each with a different grouping (partitioning).  If you have the Adventureworks database with SQL Server 2005 you can run these sample queries.

SELECT [group] CountryGroup
, name Territory
, SUM(salesytd) OVER (PARTITION BY [group], [name]) AS TerritoryTotal
, SUM(salesytd) OVER (PARTITION BY [group]) AS GroupTotal
, (SUM(salesytd) OVER (PARTITION BY [group], [name]) /
SUM(salesytd) OVER (PARTITION BY [group])) * 100.0 AS PercentofGroup
FROM sales.salesTerritory
ORDER BY [group]

The following is an equivalent query using derived table subqueries:

SELECT g.[group] CountryGroup
, t.name Territory
, TerritoryTotal
, GroupTotal
, TerritoryTotal / GroupTotal * 100.0 PercentofGroup
FROM (SELECT [group]
, SUM(salesytd) GroupTotal
FROM sales.salesterritory
GROUP BY [group]) g
INNER JOIN
(SELECT [group]
, name
, SUM(salesytd) TerritoryTotal
FROM sales.salesterritory
GROUP BY [group], name) t
ON g.[group] = t.[group]
ORDER BY [group], name

Videos You May Like

Agile Methodology in Project Management

0 153 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 487 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

Detailed Forensic Investigation of Malware Infections – April 21, 2015

4 602 5

How does an investigator hunt down and identify unknown malware? In this recording of our IT Security training webinar on April 21, 2015, Security expert Mike Danseglio (CISSP / CEH) performed several malware investigations on infected computers and identify symptoms, find root cause, and follow the leads to determine what’s happening. He demonstrated his preferred … Continue reading Detailed Forensic Investigation of Malware Infections – April 21, 2015

Write a Comment

Share your thoughts...

Please fill out the comment form below to post a reply.