HI WELCOME TO SIRIS

Difference between rows and range

Leave a Comment

In this we will discuss the difference between rows and range in SQL Server


Let us understand the difference with an example. We will use the following Employees table in this demo.

range vs rows in sql server

SQL Script to create the Employees table
Create Table Employees
(
     Id int primary key,
     Name nvarchar(50),
     Salary int
)
Go

Insert Into Employees Values (1, 'Mark', 1000)
Insert Into Employees Values (2, 'John', 2000)
Insert Into Employees Values (3, 'Pam', 3000)
Insert Into Employees Values (4, 'Sara', 4000)
Insert Into Employees Values (5, 'Todd', 5000)
Go

Calculate the running total of Salary and display it against every employee row

sql server running total query

The following query calculates the running total. We have not specified an explicit value for ROWS or RANGE clause.
SELECT Name, Salary,
        SUM(Salary) OVER(ORDER BY Salary) AS RunningTotal
FROM Employees

So the above query is using the default value which is
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

This means the above query can be re-written using an explicit value for ROWS or RANGE clause as shown below.
SELECT Name, Salary,
        SUM(Salary) OVER(ORDER BY Salary
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

We can also achieve the same result, by replacing RANGE with ROWS
SELECT Name, Salary,
        SUM(Salary) OVER(ORDER BY Salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

What is the difference between ROWS and RANGE
To understand the difference we need some duplicate values for the Salary column in the Employees table.

Execute the following UPDATE script to introduce duplicate values in the Salary column
Update Employees set Salary = 1000 where Id = 2
Update Employees set Salary = 3000 where Id = 4
Go

Now execute the following query. Notice that we get the running total as expected.
SELECT Name, Salary,
        SUM(Salary) OVER(ORDER BY Salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

running total example in sql server

The following query uses RANGE instead of ROWS
SELECT Name, Salary,
        SUM(Salary) OVER(ORDER BY Salary
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees

You get the following result when you execute the above query. Notice we don't get the running total as expected.
 range clause vs rows clause in sql server

So, the main difference between ROWS and RANGE is in the way duplicate rows are treated. ROWS treat duplicates as distinct values, where as RANGE treats them as a single entity.

All together side by side. The following query shows how running total changes
1. When no value is specified for ROWS or RANGE clause
2. When RANGE clause is used explicitly with it's default value
3. When ROWS clause is used instead of RANGE clause

SELECT Name, Salary,
        SUM(Salary) OVER(ORDER BY Salary) AS [Default],
        SUM(Salary) OVER(ORDER BY Salary
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Range],
        SUM(Salary) OVER(ORDER BY Salary
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Rows]
FROM Employees

calculate cumulative total in sql server

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.