sqlANYcountover
sql
SELECT order_id,
customer_id,
COUNT(*) OVER () AS total_rows
FROM orders;A window aggregate keeps detail rows intact while computing a value across the full result set.
SQL Window Functions: OVER Clause, PARTITION BY, ORDER BY, and Frames
Core window function syntax, partitions, ordering, and frame clauses for analytic SQL queries.
Export
Copy the compact sheet, download it, or print it.
Download
`D` dense toggle · `C` copy all
OVER clause fundamentals
Start with the smallest patterns that make window functions click.
sqlANYsumpartition
sql
SELECT order_id,
customer_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;`PARTITION BY` resets the calculation for each logical group.
sqlANYrow_numberorder by
sql
SELECT order_id,
customer_id,
created_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS rn
FROM orders;`ORDER BY` inside the window defines sequence for ranking and frame-aware calculations.
sqlANYwindowreuse
sql
SELECT order_id,
amount,
SUM(amount) OVER w AS running_sum,
AVG(amount) OVER w AS running_avg
FROM orders
WINDOW w AS (
PARTITION BY customer_id
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);Named windows improve readability when several expressions share the same partition, order, and frame.
sqlANYrowsframe
sql
SELECT created_at,
amount,
SUM(amount) OVER (
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;Use `ROWS` when you want frame boundaries to follow row positions rather than peer groups.
sqlANYrangepeers
sql
SELECT score,
COUNT(*) OVER (
ORDER BY score
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_count
FROM exam_results;`RANGE` can treat rows with equal sort values as peers, which often changes cumulative results compared with `ROWS`.
sqlANYframehistorical
sql
SELECT created_at,
amount,
AVG(amount) OVER (
ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS avg_before_current
FROM orders;This pattern is useful when you need a historical benchmark without including the current row in the average.
Frame clause cheatsheet
Quick examples of common frame shapes.
sqlANYframe
sql
ROWS BETWEEN CURRENT ROW AND CURRENT ROWUseful in examples and to contrast with broader frames.
sqlANYmoving-window
sql
ROWS BETWEEN 6 PRECEDING AND CURRENT ROWA classic moving window for row-based smoothing.
sqlANYfollowing
sql
ROWS BETWEEN CURRENT ROW AND 3 FOLLOWINGHandy for queueing, forecast windows, or lead-like summaries.
sqlANYpartition
sql
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGFor some functions, this makes intent explicit and avoids dialect-specific defaults.
More in SQL Window Functions
SQL Window Functions: Performance, EXPLAIN, and Dialect Notes
How to tune window queries, reduce sorts, structure indexes, and account for cross-dialect differences.
SQL Window Functions: Deduplication, Gaps and Islands, and Sessionization
Use ROW_NUMBER, LAG, and running groups to deduplicate rows, detect gaps, and form islands or sessions.
SQL Window Functions: Percentiles, Bucketing, and Distribution Analysis
Percent rank, cumulative distribution, percentile-style bucketing, and segmentation patterns.
SQL Window Functions: FIRST_VALUE, LAST_VALUE, NTH_VALUE, and Positional Analytics
Positional window functions for first, last, and nth values inside ordered partitions.
SQL Window Functions: LAG, LEAD, Deltas, and Time-Series Comparisons
Compare rows to prior or next rows with LAG and LEAD for change detection, retention, and trend analysis.
SQL Window Functions: Running Totals, Moving Averages, and Cumulative Metrics
Rolling metrics, cumulative sums, moving averages, and period-over-period analytics with frame clauses.