Window Functions Aggregates COUNT OVER · headcount · running

COUNT Window Function

COUNT with an OVER() clause adds row counts as a per-row column. Group sizes, running counters, and the COUNT(*)-vs-COUNT(col) NULL behaviour — plus the no-DISTINCT-allowed gotcha.

COUNT as a window function

COUNT as a window function counts rows in each window. Same three flavours as the regular aggregate — COUNT(*), COUNT(column), and the differences NULLs make — but now the count appears alongside every row instead of collapsing them.

It's the most common way to add headcount or running-counter context without losing detail.

Syntax

COUNT(* | expression) OVER (
    [PARTITION BY column_list]
    [ORDER BY column_list]
    [frame_clause]
)

Variant	What it counts
COUNT(*)	Every row in the window.
COUNT(column)	Rows where that column is not NULL.

⚠ COUNT(DISTINCT col) is not allowed inside OVER(). The DISTINCT keyword is rejected by every window aggregate. For distinct counts, fall back to a subquery or CTE.

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 — total headcount on every row

SQL

SELECT first_name,
       department_id,
       COUNT(*) OVER () AS company_headcount
FROM   employees;

Example 2 — count per department

SQL

SELECT first_name,
       department_id,
       salary,
       COUNT(*) OVER (PARTITION BY department_id) AS dept_size
FROM   employees
ORDER  BY department_id;

Each employee row also shows how many people are in their department.

Example 3 — running counter

With ORDER BY, COUNT(*) turns into a running row counter — useful for numbering rows that don't have a sequence column.

SQL

SELECT first_name, salary,
       COUNT(*) OVER (ORDER BY salary DESC) AS rank_position
FROM   employees;

💡 For row numbering, prefer ROW_NUMBER(). It's clearer and handles ties more predictably. COUNT(*) OVER (ORDER BY ...) increments by however many rows share the current ordering value.

Example 4 — COUNT(*) vs COUNT(column)

If a column has NULLs, the two forms differ — same as in regular aggregates. Imagine some employees lack a manager_id (top of org chart):

SQL

SELECT first_name,
       manager_id,
       COUNT(*)             OVER () AS total_rows,
       COUNT(manager_id)    OVER () AS rows_with_manager
FROM   employees;

Recap

COUNT(*) OVER () — total row count tagged on each row.
COUNT(*) OVER (PARTITION BY col) — group size per row.
COUNT(*) OVER (ORDER BY col) — running counter; for row numbering, prefer ROW_NUMBER.
COUNT(column) excludes NULLs; COUNT(*) doesn't.
COUNT(DISTINCT …) OVER () is not supported.