COUNT() OVER
SQL · Window Functions
Theory
COUNT() OVER (...) counts rows in the window and writes the count on each row. COUNT(*) OVER (PARTITION BY dept) gives every employee their department's headcount, and COUNT(*) OVER () gives the total number of rows in the result. The same NULL rule as the aggregate applies: COUNT(column) OVER skips rows where that column is NULL, while COUNT(*) counts them.
This is useful for adding context to detail rows, such as 'Asha works in a team of 3', or for filtering groups by size while keeping the rows: wrap the query and keep rows where dept_count >= 2. With ORDER BY inside OVER, COUNT becomes a running count (how many rows so far). Some databases support COUNT(DISTINCT col) OVER, but many, including SQL Server, Postgres and SQLite, do not, so plan to use DENSE_RANK tricks or a subquery for that.
Why we use it
Group sizes are useful context next to detail rows: team size, number of orders per customer, how many rows share a key (for finding duplicates). COUNT() OVER provides that without a GROUP BY that would hide the detail.
When to use it
Use COUNT() OVER to show group size on every row or to find duplicates (count > 1) while keeping the rows. Use GROUP BY COUNT when you only want one count per group.
How it works
- Rows split by PARTITION BY
- Is it COUNT(*)? Yes: Count every row in the frame. No: Count only non-NULL values.
- ORDER BY inside OVER? Yes: Running count so far. No: Count of whole partition.
- Write the count on each row
- Detail rows kept, count added
Syntax
COUNT(*) OVER (PARTITION BY col)Example
SELECT name, department,
COUNT(*) OVER (PARTITION BY department) AS dept_headcount
FROM employees;Key points
- Adds a per-group count to every row of that group
- COUNT(column) OVER() skips NULLs like the aggregate form does
- COUNT(*) OVER () = total rows in the result, handy for paging
- With ORDER BY it becomes a running count
- COUNT(DISTINCT) OVER is not supported in many databases