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

A Simple Introduction to Cisco CML2

0 3640 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Cable Testers and How to Use them in Network Environments

0 645 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

OSPF Adjacency Troubleshooting Solution – Getting Close to the OSPF adj

0 246 1

In this video, Cisco CCNA & CCNP instructor Mark Jacob shows how to troubleshoot OSPF Adjacency issues by showing the distance between routers with the show ip ospf neighbor command.

Write a Comment

Share your thoughts...

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