Window Functions Aggregates AVG OVER · rolling avg · diff

AVG Window Function

The window-function flavour of AVG. Each row keeps its detail and picks up the average of its window — perfect for comparisons against group, running, or rolling averages.

AVG as a window function

AVG as a window function gives every row the average of its window — typically used to compare each row against its peers (department average, rolling average, year-to-date average) without losing the row-level detail.

Same NULL behaviour as the regular aggregate: NULLs are skipped, not treated as zero. An empty window returns NULL.

Syntax

AVG(expression) OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

Demo dataset

SQL — Create & insert

CREATE TABLE employees (
    employee_id    INT,
    first_name     VARCHAR(50),
    last_name      VARCHAR(50),
    salary         DECIMAL(10, 2),
    manager_id     INT,
    department_id  INT
);

INSERT INTO employees VALUES
(200, 'Jennifer',  'Whalen',     4400.00, 101, 1),
(201, 'Michael',   'Hartstein', 13000.00, 100, 2),
(202, 'Pat',       'Fay',        6000.00, 201, 2),
(203, 'Susan',     'Mavris',     6500.00, 101, 4),
(204, 'Hermann',   'Baer',      10000.00, 101, 4),
(205, 'Shelley',   'Higgins',   12008.00, 101, 1),
(206, 'William',   'Gietz',      8300.00, 205, 1),
(100, 'Steven',    'King',      24000.00, NULL, 9),
(101, 'Neena',     'Kochhar',   17000.00, 100, 9),
(102, 'Lex',       'De Haan',   17000.00, 100, 9);

Example 1 — overall average alongside each row

SQL

SELECT first_name,
       salary,
       ROUND(AVG(salary) OVER (), 2) AS company_avg
FROM   employees;

Example 2 — department average + delta

SQL

SELECT first_name,
       department_id,
       salary,
       ROUND(AVG(salary) OVER (PARTITION BY department_id), 2) AS dept_avg,
       salary - AVG(salary) OVER (PARTITION BY department_id) AS diff
FROM   employees
ORDER  BY department_id, salary DESC;

Each row carries its department's average and how far above or below it the employee sits — a comparison that's painful to write any other way.

Example 3 — rolling 3-row average

A common dashboard pattern: smooth out noise by averaging each value with the two before it.

SQL

SELECT employee_id,
       salary,
       ROUND(AVG(salary) OVER (
           ORDER BY employee_id
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 2) AS rolling_3_avg
FROM   employees
ORDER  BY employee_id;

Example 4 — running average

SQL

SELECT employee_id, salary,
       ROUND(AVG(salary) OVER (ORDER BY employee_id), 2) AS running_avg
FROM   employees
ORDER  BY employee_id;

With ORDER BY but no explicit frame, you get a running average — each row's value is the mean of itself and everything before it.

Recap

AVG(...) OVER (PARTITION BY col) — group average, every row keeps detail.
Subtract from the row's value to get a "diff from average" column.
Use a frame clause for rolling/centred averages.
NULLs are skipped; empty windows return NULL.