25 Jan 2017 Jeffrey J. Keller Grouping, Rolling, and Cubing Data The first two articles in this series highlighted SQL Server's ability to organize data into user-defined windows and its aggregate functions. Part 3 will focus on other methods of aggregating and organizing data using built-in SQL Server features – specifically, its grouping functions. For consistency, the same base data will be used as in the first two parts of this series. CREATE TABLE WindowTable (WindowID INT IDENTITY, House Varchar(32), FullName Varchar(64), PhysicalSkill Decimal(4, 2), MentalSkill Decimal (4, 2)) GO Read more 9 Aug 2016 Jeffrey J. Keller Window Functions: Part Two: Aggregating Data In part one of this series on SQL Server window functions, the functionality of the OVER() clause was discussed. This article will focus on aggregate functions. The majority of these can be used in conjunction with OVER. Some, like SUM() and AVG(), are widely used. Others — including VARP() and CHECKSUM_AGG() — are not as well-known, but are potentially quite useful. OVER defines the user-specified range on which a function is applied. Read more 21 Jun 2016 Jeffrey J. Keller Window Functions in SQL Server: Part One: The OVER() Clause Window functions were first introduced in standard SQL 2003 and then extended in SQL 2008. Since SQL Server 2005, Microsoft has been steadily improving window functions in the database engine. These functions perform an aggregate operation against a user-defined range of rows (the window) and return a detail-level value for each row. Well-known aggregate functions include SUM, AVG, MIN, MAX, and many others. Recent versions of SQL Server have introduced ranking and analytic functions such as LAG, LEAD, RANK, CUME_DIST, and many others. Read more