SQLSERVER over()

The OVER() clause in SQL Server is used in conjunction with window functions to define the partitioning and ordering of the rows within the result set. It allows the developer to define a window or subset of the data over which the window function should operate.

Syntax:

SELECT column1, column2, ..., columnN,
window_function() OVER ( [PARTITION BY partition_expression, ... ]
[ORDER BY order_expression [ASC|DESC], ... ]
[ROWS { UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW | n FOLLOWING | UNBOUNDED FOLLOWING } ]
)
FROM table_name;

Example:

SELECT customer_name, order_date, order_amount,
SUM(order_amount) OVER (PARTITION BY customer_name
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

This query will return the customer name, order date, order amount, and the running total of the order_amount for each customer, with the running total being calculated by summing the order_amount for each customer up to the current row. The PARTITION BY clause partitions the data by customer_name, and the ORDER BY clause orders the data by order_date. The ROWS BETWEEN clause defines the range of rows over which the running total should be calculated.