SQL Server Over() Partition By

 in SQL Server  tagged running balance / running total / over / total / order by

Sum and Over

This post will discuss the first two of three components of over(). The three components are:

  • partition by
  • order by
  • “rows between”

First, let’s look at how we can add up values in a column. The query uses SUM(). Let’s start with creating a new table and adding values into it. This post is based on Phillip Burton’s SQL 70-461 course on Udemy.com, session 5. I have changed the data a bit and changed the table name and column names. Here is the script to create the table and add at the bottom of this post is the script to add the values to the table. 

create table CustomerSalesByMonth
(CustomerNumber int,
SalesMonth date,
Amount numeric(14,2),
CONSTRAINT PK_CustomerSalesByMonth PRIMARY KEY(CustomerNumber,SalesMonth))

The query to add up a column is as follows. We use SUM(). That just gives us a single number as a result. 

select sum(Amount) from [dbo].[CustomerSalesByMonth]

Suppose we want to also show the columns in the table. Here is the query. 

select [CustomerNumber],  year(SalesMonth) as YearSales,
sum(Amount) as TotalAmount
from [dbo].[CustomerSalesByMonth]
group by [CustomerNumber], year(SalesMonth)  -- cannot use alias in group by
order by [CustomerNumber], YearSales  -- can use alias in order by

Here are the results. Also, know that you cannot rely on the group by to put the data in the order that you might expect. Always add order by.

Over()

Let’s change the query and use Over(). Here we do not need a Group By. Also, there is nothing inside the brackets in our Over(). 

select CustomerNumber,  SalesMonth,
sum(Amount) over() as TotalAmount
from [dbo].[CustomerSalesByMonth]

Here are the results.

Partition By

When we add partition by inside the over, we refine the range (or rows) that we are going to take. Instead of looking at the whole table (after any where clause), we are only looking at sections of a table. Here is another example.

Running Totals

We can create a column with running totals. The syntax is a bit surprising, however. We use order by inside over(). I have left in the other ones for comparison. 

select CustomerNumber, SalesMonth, Amount, 
sum(Amount) over() as TotalAmount,
sum(Amount) over(partition by [CustomerNumber]) as TotalAmountofCustomer,
sum(Amount) over(partition by [CustomerNumber] order by SalesMonth) as RunningTotalAmountofCustomer
from [dbo].[CustomerSalesByMonth]

Here are the results from SSMS. It is possible to have a descending running total by putting DESC after SalesMonth inside the Over(). You could then, at the bottom of the query, do an overall ORDER BY CustomerNumber, SalesMonth.

Inserting the 2016 Data

Below is the code to add values to the table. It’s down here at the bottom just to tuck it away out of the more important stuff. 

INSERT INTO CustomerSalesByMonth(CustomerNumber, SalesMonth, Amount)
VALUES (123, '2016-01-01', 1409.02), 
(123, '2016-02-01', 1222.99), 
(123, '2016-03-01', 23.45), 
(123, '2016-04-01', 2111.88), 
(123, '2016-05-01', 2320.19), 
(123, '2016-06-01', 1410.73), 
(123, '2016-07-01', 1676.84), 
(123, '2016-08-01', 3415.98), 
(123, '2016-09-01', 4214.05), 
(123, '2016-10-01', 2008.87), 
(123, '2016-11-01', 2403.67), 
(123, '2016-12-01', 1454.91)

INSERT INTO CustomerSalesByMonth(CustomerNumber, SalesMonth, Amount)
VALUES (124, '2016-01-01', 9009.02), 
(124, '2016-02-01', 1422.99), 
(124, '2016-03-01', 232.45), 
(124, '2016-04-01', 8111.88), 
(124, '2016-05-01', 1320.19), 
(124, '2016-06-01', 3410.73), 
(124, '2016-07-01', 1133.76), 
(124, '2016-08-01', 1523.99), 
(124, '2016-09-01', 1444.45), 
(124, '2016-10-01', 2009.93), 
(124, '2016-11-01', 2412.03), 
(124, '2016-12-01', 1434.84)

Partition By Multiple

To illustrate this, we need to add more data. We will have more than one year’s worth of data. After adding the data we will have two customers, each with two years of data. We are going to partition by the Customer number and by the year. So now the running total will restart for each Customer and for each Year. See below for the query that inserts the data for the year 2015. What is the query that does this type of partition? I have left in the other queries for comparision. 

select CustomerNumber, SalesMonth, Amount, 
sum(Amount) over() as TotalAmount,
sum(Amount) over(partition by [CustomerNumber]) as TotalAmountofCustomer,
sum(Amount) over(partition by [CustomerNumber] order by SalesMonth) as RunningTotalAmountofCustomer,
sum(Amount) over(partition by [CustomerNumber], year(SalesMonth) order by SalesMonth) as RunningTotalAmountofCustomerYear
from [dbo].[CustomerSalesByMonth]

Here are the results from SSMS.

Inserting the 2015 Data

INSERT INTO CustomerSalesByMonth(CustomerNumber, SalesMonth, Amount)
VALUES (123, '2015-01-01', 2456.02), 
(123, '2015-02-01', 4222.99), 
(123, '2015-03-01', 4323.45), 
(123, '2015-04-01', 1111.88), 
(123, '2015-05-01', 1320.19), 
(123, '2015-06-01', 2410.73), 
(123, '2015-07-01', 3676.84), 
(123, '2015-08-01', 1415.98), 
(123, '2015-09-01', 2214.05), 
(123, '2015-10-01', 1008.87), 
(123, '2015-11-01', 1403.67), 
(123, '2015-12-01', 2454.91)

INSERT INTO CustomerSalesByMonth(CustomerNumber, SalesMonth, Amount)
VALUES (124, '2015-01-01', 1809.02), 
(124, '2015-02-01', 2422.99), 
(124, '2015-03-01', 2332.45), 
(124, '2015-04-01', 5111.88), 
(124, '2015-05-01', 3320.19), 
(124, '2015-06-01', 2410.73), 
(124, '2015-07-01', 3133.76), 
(124, '2015-08-01', 2523.99), 
(124, '2015-09-01', 5444.45), 
(124, '2015-10-01', 3009.93), 
(124, '2015-11-01', 2412.03), 
(124, '2015-12-01', 7434.84)