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;
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.
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
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;
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.