With the 2005 and 2012 releases of SQL Server, Microsoft gave us several new functions with amazing capabilities. These functions are part of the ANSI standards, and have nothing to do with the Windows OS. Some of these functions are well known, for example ROW_NUMBER is used by many people. In addition to ROW_NUMBER, we now have the ability to calculate summaries without changing a query to an aggregate query, calculate running totals, pull values from other rows, and perform statistical calculations. This course covers all of the available window functions including syntax, real world examples, and performance considerations.
Kathi Kellenberger is a SQL Server MVP and a Teammate with Linchpin People. She is the author or co-author of several SQL Server books, including Beginning T-SQL from Apress, and frequently speaks at community events such as PASS Summit and SQL Saturday.
Using Accumulating Window Aggregates Hello. My name is Kathi Kellenberger with Linchpin People and I'm recording this course for Pluralsight. This is the T-SQL Window Function Course and you're watching the Using Accumulating Window Aggregates Module. In 2005, Microsoft made Window Aggregate Functions available. They give you the ability to add aggregate functions to non-aggregate queries. To calculate things like subtotals, grandtotals or overall averages. They are really easy to use and make your codes simpler to understand. The 2005 Window Aggregate Functionality does not allow an ORDER BY and the OVER clause. And when you think about it, why would you need one? You don't need to order the rows in order to calculate a sum or an average. One of the enhancements in 2012 is just that, support for an ORDER BY when using a Window Aggregate. I just said that you don't need to order the rows to calculate a sum or average. But in 2012, adding the ORDER BY gives you completely different functionality. This functionality is very different than what we had before. And it definitely does depend on the order of the rows. So what can we do with this? The most basic reasons are to calculate a running total or a moving average by just adding an ORDER BY to the Window aggregate sum, we can get a running total very easily. By adding PARTITION BY, we can have the running total start over for each customer for example. Let's switch to management studio and take a look at some code.