Revision history [back]

Window aggregate functions can be utilized in SQL by first specifying a window (or partition) within the query using the "OVER" clause, and then applying the desired aggregate function to the window. For example, consider the following query:

SELECT department, employeename, salary, AVG(salary) OVER(PARTITION BY department) as avgsalary FROM employees

In this query, we specify a window partitioned by department, and then apply the AVG() function to the salary column within that window. This calculates the average salary for each department separately and returns it as a new column alongside the original data. Other window aggregate functions include SUM(), MIN(), MAX(), COUNT(), and ROW_NUMBER().