SQL - How to use count() window function?

Here’s a clear and practical explanation of how to use COUNT() as a window function in SQL, with examples for common databases such as PostgreSQL, MySQL 8+, SQL Server, Oracle, and Spark SQL.

---

✅ 1. Basic Syntax

COUNT(column) OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Window COUNT() does not collapse rows; instead, it returns a count value on each row.

---

✅ 2. COUNT() OVER() Without PARTITION or ORDER

This gives you the total row count of the entire query result:

SELECT
    name,
    COUNT(*) OVER() AS total_count
FROM employees;

Result example:

name	total_count
Alice	5
Bob	5
Carol	5

---

✅ 3. COUNT() OVER(PARTITION BY …)

This gives a group count but keeps original rows.

SELECT
    department,
    name,
    COUNT(*) OVER(PARTITION BY department) AS dept_count
FROM employees;

Example result:

department	name	dept_count
IT	Alice	3
IT	Bob	3
IT	Carol	3
HR	Dan	2
HR	Emma	2

Equivalent to GROUP BY department, but without losing row detail.

---

✅ 4. COUNT() OVER(ORDER BY …) — Running Count

This creates a running / cumulative count:

SELECT
    order_id,
    order_date,
    COUNT(*) OVER(ORDER BY order_date) AS running_count
FROM orders;

Example:

order_date	running_count
2024-01-01	1
2024-01-05	2
2024-01-09	3

---

✅ 5. PARTITION + ORDER = Running Count per Group

SELECT
    department,
    name,
    COUNT(*) OVER(
        PARTITION BY department
        ORDER BY hire_date
    ) AS running_count_in_dept
FROM employees;

Example:

department	name	running_count_in_dept
IT	Alice	1
IT	Bob	2
IT	Carol	3
HR	Dan	1
HR	Emma	2

---

✅ 6. COUNT() With ROWS BETWEEN

This lets you count within a specific window frame, for example last N rows or last N days.

Example: count of last 3 rows:

SELECT
    id,
    value,
    COUNT(*) OVER(
        ORDER BY id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rows_last_3
FROM t;

If rows are:

id	value
1	a
2	b
3	c
4	d

Result:

id	rows_last_3
1	1
2	2
3	3
4	3

---

⚠️ COUNT(column) vs COUNT(*)

COUNT(column)

Counts only non-NULL values.

COUNT(*)

Counts all rows, even if column is NULL.

---

🔍 Spark SQL Example

Spark also supports window COUNT():

SELECT
    department,
    name,
    COUNT(*) OVER(PARTITION BY department ORDER BY name)
FROM employees;

Or in PySpark:

from pyspark.sql.window import Window
from pyspark.sql import functions as F

w = Window.partitionBy("department").orderBy("name")

df.withColumn("count_in_dept", F.count("*").over(w)).show()