Tag Archives: SQL

SQL Tip: Windowing Functions

• ,

A simple SELECT returns all relevant rows from the database. GROUP BY applies aggregate functions, condensing SELECTed data into one summary row per grouping. What if mixing the two approaches is desired—what if data rows need to be combined with aggregate totals in the same result set?

T-SQL’s OVER clause allows several kinds of functions to be applied to non-GROUPed BY rows. Using OVER, a simple SELECT returning data rows can be expanded to include columns containing summary statistics. Traditionally, aggregate functions can only be used in conjunction with GROUP BY or to produce a single row summary result set; when used with OVER, these limitations are removed. Continue reading →

SQL Tip: Double Duty for DECLARE

• ,

Just recently, I realized that T-SQL allows variables to be declared and set with a single statement:

DECLARE @UserID int = 1592;

Up until this discovery, I only knew about the more traditional, two-step declaration and assignment syntax:

DECLARE @UserID int;
SET @UserID = 1592;

Continue reading →