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
(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 171 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

Creating Users and Managing Passwords in Microsoft Office 365

0 740 4

In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365.   For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365    

Windows 10 Features and Navigation – December 1, 2015

0 128 1

In this recorded Windows 10 webinar from December 1,2015, Windows Instructor Steve Fullmer presents the navigation and some of the new features associated with Windows 10 including Sysinternals Tools for Windows Client, Windows core concepts, exploring Process Explorer as well as some of the features that are not yet ready for prime time but will … Continue reading Windows 10 Features and Navigation – December 1, 2015

Write a Comment

Share your thoughts...

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