Window functions are closely related to aggregate functions except that it retains all the rows.

Categories

Aggregate Window Functions

Ranking Window Functions

Value Window Functions

Example

with sales_tbl as (
select sales.*
	from (VALUES
		('Spiderman',1,19750),
		('Batman',1,19746),
		('Superman',1,9227),
		('Iron Man',1,9227),
		('Wonder Woman',2,16243),
		('Kikkoman',2,17233),
		('Cat Woman',2,8308),
		('Ant Man',3,19427),
		('Aquaman',3,16369),
		('Iceman',3,9309)
	) sales (emp_name,dealer_id,sales)
)
select ROW_NUMBER() over (order by dealer_id) as rownumber,
	*,
	AVG(sales) over (partition by dealer_id) as [Average Sales by DealerID],
	SUM(sales) over (partition by dealer_id) as [Total Sales by DealerID],
	SUM(sales) over (partition by dealer_id order by sales rows between unbounded preceding and current row) as [Running Total by DealerID]
from sales_tbl