Using OVER() with Aggregate Functions

One of new features in SQL 2005 that I haven't seen much talk about is that you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Unfortunately, it isn't especially powerful, and you can't do running totals with it, but it does help you make your code a little shorter and in many cases it might be just what you need.
The way it works is similar to joining an aggregated copy of a SELECT to itself. For example, consider the following:

You can now easily return the total CNT per Area as an additional column in this SELECT, simply by adding an aggregate SUM() function with an OVER() clause:

for Example

select

Area, nvcQuestionName, [CNT], nvcResultDesc,Sum(CNT) OVER (Partition by Area) as Total from sample1