Problem using OVER PARTITION clause

Hi all

I'm trying to output some financials and also get a running total. I've looked at many different explanations of the OVER PARTITION clause, and have tried to incorporate it into my SQL statement. However, I get the error "Incorrect syntax near 'order'" when I try to run this.

Could anyone please tell me what I'm doing wrong?

Many thanks

select det.SERIALNUMBER, det.dateofpayment, det.paymentamount, sum(det.PAYMENTAMOUNT) over (partition by det.paymentamout order by det.serialnumber) AS RUNNING
from batchdetail

The syntax seems correct to me, except for the missing table alias - see the last line below:

SELECT det.SERIALNUMBER,
       det.dateofpayment,
       det.paymentamount,
       SUM(det.PAYMENTAMOUNT) OVER (PARTITION BY det.paymentamout ORDER BY det.serialnumber) AS RUNNING
FROM batchdetail AS det;
2 Likes

Hi James

Thanks for this. It' still not working though, so I wonder if my version of SQL Server (2008 R2) doesn't work with OVER PARTITION.

Best wishes
Jon

The aggregate window function with an order by clause that you have in the code is available only in SQL 2012 or later (I think). It definitely is not available in SQL 2008 R2. You would have to use some other means for calculating the running total.

You can use a subquery, but that is likely to be slow. So it would not be a good solution if you have a large number of rows. Another option is the so-called quirky update. Look up articles that @JeffModen has written on it - I don't seem to be able to find the link at the moment.

1 Like

Looks like over clause was introduced in SQL SERVER 2005

i tried to test it
Looks like there is a TYPO thats the issue


It should be amount !!!

please click arrow for DROP Create Sample DATA
drop table #batchdetail 
go 

create table #batchdetail
(
SERIALNUMBER int , 
dateofpayment date , 
PAYMENTAMOUNT int 
)
go 

insert into #batchdetail select 1,'2019-09-09',1000
insert into #batchdetail select 1,'2019-10-11',3000
insert into #batchdetail select 1,'2019-11-13',400

insert into #batchdetail select 2,'2019-11-09',500
insert into #batchdetail select 2,'2019-07-11',450

go 



select 'sample data',* from #batchdetail
go

select   
         'SQL Output',
         det.SERIALNUMBER
	   , det.dateofpayment
	   , det.paymentamount
       , sum(det.PAYMENTAMOUNT) over (partition by det.paymentamount order by det.serialnumber) AS RUNNING
from 
   #batchdetail det 

Thanks Harishgg. Unfortunately this still doesn't work. It seems very odd, especially if OVER PARTITION was released in the 2005 edition.

Please show in what way it doesn't work or what error message is produced.

I think ROWS became part of OVER in SQL2012:

SELECT D.SerialNumber, D.DateOfPayment, D.PaymentAmount
	,SUM(D.PaymentAmount) OVER (ORDER BY D.SerialNumber ROWS UNBOUNDED PRECEDING) AS Running
FROM BatchDetail D;

For SQL2008 you will have to try something triangular like:

SELECT D.SerialNumber, D.DateOfPayment, D.PaymentAmount
	,X.Running
FROM BatchDetail D
	CROSS APPLY
	(
		SELECT SUM(D1.PaymentAmount) AS Running
		FROM BatchDetail D1
		WHERE D1.SerialNumber <= D.SerialNumber
	) X;
1 Like

This is true, but window functions were only partially implemented in SQL 2005. SQL 2015 supported aggregate functions without an order by clause, for example

SUM(det.PAYMENTAMOUNT) OVER (PARTITION BY det.paymentamout)

SQL 2012 added the ORDER BY clause, so you can do the following

SUM(det.PAYMENTAMOUNT) OVER (PARTITION BY det.paymentamout ORDER BY det.serialnumber)

When you have the ability to add the order by clause, it adds a significant amount of functionality such as the ability to calculate the running total, and other things by specifying the range/number of rows to be included in the aggregation, as @Ifor pointed out.

1 Like