🧠 SQL Window Frames: Which Rows Are Included?

Xorthax now has totals, counts, and averages—but something still isn’t quite right. Every calculation uses all rows in the group.

What if he only wants to look at some of the rows?

That’s where window frames come in.

🚀 Window Frames + OVER()

A window frame controls which rows are included in the calculation.

FUNCTION(...) OVER (
  PARTITION BY column
  ORDER BY column
  ROWS BETWEEN ...
)

This lets you define a subset of rows relative to the current row.

• All previous rows
• A fixed number of rows
• Rows before and after

📊 Example: Limiting the Rows

🧾 Sample Data

Now apply a frame:

SELECT OrderDate, Amount,
       SUM(Amount) OVER (
         ORDER BY OrderDate
         ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS Value
FROM Orders;

Result:

Each row now only looks at itself and the row before it.

🧠 What’s Happening Here?

ORDER BY OrderDate creates a sequence.

ROWS BETWEEN 1 PRECEDING AND CURRENT ROW defines the frame:

• 1 PRECEDING → one row before
• CURRENT ROW → the current row

So each calculation uses a small window that moves row by row.

By changing the frame, you control exactly which rows are included.

🧪 Practice What You Just Learned

SELECT orderid, orderdate, totalamount, SUM(totalamount) OVER (ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS two_order_sum FROM orders ORDER BY orderdate;

➡️ Next Step

Now that you can control which rows are included, you can build more useful patterns.

Next, you’ll use this to calculate values that build over time.

Want to go deeper?