SQL WINDOW Function

January 30, 2023

Like the Aggregate function, the WINDOW Function also aggregates the values in the particular window (or, set of rows ) and it returns the aggregated values for each row. These functions use the OVER () clause to define the window mentioned in the query. The OVER clause partitions the rows to form a set of rows whereas it also defines the order of rows within the partition.

Syntax of WINDOW Function –

SELECT columnA WINDOW FUNCTION (columnB) OVER([PARTITION BY columnA] [ORDER BY columnC]) AS new_column FROM table_name;

NOTE –

In the above syntax, the WINDOW FUNCTION can be any aggregate or ranking function.
“columnA” represents which column is to be selected.
“columnB” with the window function represents which column is associated with the function.
“columnC” represents the column on basis of which the partition will be done.

AGGREGATE WINDOW Function

As discussed earlier, the aggregate functions such as SUM(), COUNT(), AVERAGE(), etc. are applied over a set of rows then these are known as the AGGREGATE WINDOW Function.

DEMO TABLE –

Product Table

Category	Region	City	Country	Sales	Customer_name
Office Supplies	Central	Chicago	United States	15.696	Suzanne McNair
Technology	East	New York City	United States	539.97	Amy Hunt
Office Supplies	East	New York City	United States	22.58	Amy Hunt
Office Supplies	East	Dover	United States	16.52	Emily Phan
Office Supplies	East	Dover	United States	671.94	Emily Phan
Technology	West	Aurora	United States	169.064	Tracy Poddar
Office Supplies	West	Aurora	United States	168.624	Tracy Poddar
Office Supplies	East	Long Beach	United States	19.44	Anthony Rawles
Office Supplies	West	Pasadena	United States	31.92	Helen Andreada
Furniture	West	Pasadena	United States	433.568	Helen Andreada

Example – Consider the above Product table of the Customer database and evaluate the Sum of Sales for each Category.

SELECT Category,Region,City,[Country/Region],[Customer Name], SUM(Sales) OVER (PARTITION BY Category ) AS Total_sales FROM [dbo].[Products];

The output after executing the above statement is as shown –

Category	Region	City	Country	Customer_ID	Customer_name	Total_sales
Office Supplies	Central	Chicago	United States	SM-20950	Suzanne McNair	946.72
Office Supplies	East	New York City	United States	AH-10465	Amy Hunt	946.72
Office Supplies	East	Dover	United States	EP-13915	Emily Phan	946.72
Office Supplies	East	Dover	United States	EP-13915	Emily Phan	946.72
Office Supplies	West	Aurora	United States	TP-21565	Tracy Poddar	946.72
Office Supplies	East	Long Beach	United States	AR-10825	Anthony Rawles	946.72
Office Supplies	West	Pasadena	United States	HA-14920	Helen Andreada	946.72
Technology	East	New York City	United States	AH-10465	Amy Hunt	709.034
Technology	West	Aurora	United States	TP-21565	Tracy Poddar	709.034
Furniture	West	Pasadena	United States	HA-14920	Helen Andreada	433.568

As shown in the above query, the sum of sales within each category is displayed in the column Total_sales.

RANKING WINDOW Function

The Ranking Window function also aggregates the set of values or a particular window and returns the aggregated value for each row but unlike the aggregated function, it returns the rank for each row in the result set based on the specified values in those rows.

Below are some of the Ranking Window functions –

1. RANK () – The RANK() function assigns a unique rank for each row in the result set. If two records have the same value then it will assign the same rank by skipping the next rank in the sequence.

2. ROW_NUMBER () – It assigns a unique sequential rank to each row in the result set. The row_number will be reset with each partition defined by the PARTITION clause.

3. DENSE_RANK () – It assigns a unique rank to each row in the result set. It is identical to the RANK() but does not skip any rank. If two records have the same value then it will assign the same rank. The next ranking sequence number will be assigned to the following rows.