SQL Server 2005 OVER Clause
SQL Server 2005 OVER Clause
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
ORDER BY [group]
The following is an equivalent query using derived table subqueries:
SELECT g.[group] CountryGroup
, t.name Territory
, TerritoryTotal / GroupTotal * 100.0 PercentofGroup
FROM (SELECT [group]
, SUM(salesytd) GroupTotal
GROUP BY [group]) g
, SUM(salesytd) TerritoryTotal
GROUP BY [group], name) t
ON g.[group] = t.[group]
ORDER BY [group], name
You May Also Like
A Simple Introduction to Cisco CML20 3703 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 Environments0 668 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 adj0 247 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.