Running total error

Question

Post reply

Running total error

pwalter83

SSChampion

Points: 14575
More actions
June 20, 2014 at 3:53 am

#292521

Hi,
I am trying to calculate running total in sql as its not giving me the desired result on SSRS (on the fly calculation).
I tried using the following code but it returned with an error - Incorrect syntax near 'order'.
SUM(USD_AMOUNT) OVER ( PARTITION BY comp ORDER BY GL.[WK NO]) AS [Running Total]
Could somebody please tell what modification I need to make in the above code in order to make it work ?
Thanks,
Vishal

Viewing 15 posts - 1 through 15 (of 75 total)

You must be logged in to reply to this topic. Login to reply

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 1

Is this SQL 2005 and above ?

EDIT: Whats the compatibility of the database ?

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pwalter83 SSChampion Points: 14575 More actions · Answer 2

Sachin Nandanwar (6/20/2014)
Is this SQL 2005 and above ?
EDIT: Whats the compatibility of the database ?

Thanks.

Yes, its SQL 2008 R2. The compatibility level is SQL Server 2008 (100).

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 3

Quote strange.Can you post the entire query.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pwalter83 SSChampion Points: 14575 More actions · Answer 4

Sachin Nandanwar (6/20/2014)
Quote strange.Can you post the entire query.

Please find the query attached.

Thanks.

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 5

pwalter83 (6/20/2014)
Hi,
I am trying to calculate running total in sql as its not giving me the desired result on SSRS (on the fly calculation).
I tried using the following code but it returned with an error - Incorrect syntax near 'order'.
SUM(USD_AMOUNT) OVER ( PARTITION BY comp ORDER BY GL.[WK NO]) AS [Running Total]
Could somebody please tell what modification I need to make in the above code in order to make it work ?
Thanks,
Vishal

SQL Server < 2012 does not support the extended OVER clause, there is no "windowing", hence the ORDER is not applicable for the SUM.

😎

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 6

Yes that's right..

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pwalter83 SSChampion Points: 14575 More actions · Answer 7

Sachin Nandanwar (6/20/2014)
Yes that's right..

Could you please suggest another way to do a running total in SQL 2008 R2 then ?

Thanks.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 8

But you seem to having one already in your query

(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum

FROM GL b WHERE b.[WK NO] < = a.[WK NO])T)

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pwalter83 SSChampion Points: 14575 More actions · Answer 9

Sachin Nandanwar (6/20/2014)
But you seem to having one already in your query
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
FROM GL b WHERE b.[WK NO] < = a.[WK NO])T)

I dont know, somehow this query is not calculating the sum correctly. Can you please suggest something else that is relevant ? I am stuck on this for a long time now.

Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

pwalter83 (6/20/2014)
Sachin Nandanwar (6/20/2014)
But you seem to having one already in your query
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
FROM GL b WHERE b.[WK NO] < = a.[WK NO])T)
I dont know, somehow this query is not calculating the sum correctly. Can you please suggest something else that is relevant ? I am stuck on this for a long time now.
Thanks.

Paul, there are numerous ways of calculating a running total using TSQL. The one shown above is the Triangular Join (TJ). There's also the Quirky Update (QU), cursor and recursive CTE methods. Each has pro's and cons, IIRC Jeff Moden has covered the lot in one of his many papers here[/url].

Provide a little information including ddl and dml for sample data and folks will suggest which one might be most appropriate for your needs and code it up for you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pwalter83 SSChampion Points: 14575 More actions · Answer 11

ChrisM@Work (6/20/2014)
pwalter83 (6/20/2014)
Sachin Nandanwar (6/20/2014)
But you seem to having one already in your query
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
FROM GL b WHERE b.[WK NO] < = a.[WK NO])T)
I dont know, somehow this query is not calculating the sum correctly. Can you please suggest something else that is relevant ? I am stuck on this for a long time now.
Thanks.
Paul, there are numerous ways of calculating a running total using TSQL. The one shown above is the Triangular Join (TJ). There's also the Quirky Update (QU), cursor and recursive CTE methods. Each has pro's and cons, IIRC Jeff Moden has covered the lot in one of his many papers here[/url].
Provide a little information including ddl and dml for sample data and folks will suggest which one might be most appropriate for your needs and code it up for you.

Thanks a lot for your for your reply.

Please find the DDL and sample data below:

---------------------------------------------------------

CREATE TABLE [dbo].[GL](

[Sequence] [int] IDENTITY(1,1) NOT NULL,

[WK NO] [int] NULL,

[AC] [nvarchar](10) NULL,

[COMP] [nvarchar](10) NULL,

[USD_AMOUNT] [numeric](16, 2) NULL,

[SOA_TYPE] [nvarchar](20) NULL

) ON [PRIMARY]

insert into GL

values('1','9','16201','12003','100','Agent')

insert into GL

values('2','10','16204','12003','200','Freight')

insert into GL

values('3','11','16207','12003','150','Agent')

insert into GL

values('5','12','16208','12003','250','Costs')

insert into GL

values('6','13','16224','12003','400','Cost')

insert into GL

values('7','14','16295','12003','500','Freight')

insert into GL

values('8','15','16295','12003','540','Cost')

---------------------------------------------------------

I need to do a running total which would involve doing a SUM of SUM (not allowed in SQL so have to tackle this another way). I have tried to achieve this in the attached query but without results.

Thanks.

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 12

I dont understand the problem here.Your query seems to be achieving it.

CREATE TABLE [dbo].[GL](

[Sequence] [int] NOT NULL,

[WK NO] [int] NULL,

[AC] [nvarchar](10) NULL,

[COMP] [nvarchar](10) NULL,

[USD_AMOUNT] [numeric](16, 2) NULL,

[SOA_TYPE] [nvarchar](20) NULL

) ON [PRIMARY]

insert into GL

values('1','9','16201','12003','100','Agent')

insert into GL

values('2','10','16204','12003','200','Freight')

insert into GL

values('3','11','16207','12003','150','Agent')

insert into GL

values('5','12','16208','12003','250','Costs')

insert into GL

values('6','13','16224','12003','400','Cost')

insert into GL

values('7','14','16295','12003','500','Freight')

insert into GL

values('8','15','16295','12003','540','Cost')

GO

SELECT

(SELECT SUM(YourSum) FROM

(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum

FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]

FROM GL a

GROUP BY a.[WK NO], a.COMP

ORDER BY a.[WK NO], a.COMP

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

pwalter83 SSChampion Points: 14575 More actions · Answer 13

Sachin Nandanwar (6/20/2014)
I dont understand the problem here.Your query seems to be achieving it.
CREATE TABLE [dbo].[GL](
[Sequence] [int] NOT NULL,
[WK NO] [int] NULL,
[AC] [nvarchar](10) NULL,
[COMP] [nvarchar](10) NULL,
[USD_AMOUNT] [numeric](16, 2) NULL,
[SOA_TYPE] [nvarchar](20) NULL
) ON [PRIMARY]
insert into GL
values('1','9','16201','12003','100','Agent')
insert into GL
values('2','10','16204','12003','200','Freight')
insert into GL
values('3','11','16207','12003','150','Agent')
insert into GL
values('5','12','16208','12003','250','Costs')
insert into GL
values('6','13','16224','12003','400','Cost')
insert into GL
values('7','14','16295','12003','500','Freight')
insert into GL
values('8','15','16295','12003','540','Cost')
GO
SELECT
(SELECT SUM(YourSum) FROM
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]
FROM GL a
GROUP BY a.[WK NO], a.COMP
ORDER BY a.[WK NO], a.COMP

No its not working, I just checked it. This is the resultset that I get:

WKNO AUSD FUSD CUST agent_.. WK BAL Running Total

9100.000.000.000.00100.001552722.28

100.00200.000.000.00200.006866522.21

11150.000.000.000.00150.008658921.50

120.000.000.00250.00250.00-2440896.12

130.000.00400.000.00400.00-3911241.30

140.00500.000.000.00500.004083099.88

150.000.00540.000.00540.009459770.45

The Running total is all incorrect. I dont why its showing values in millions and decimals. Do you have any idea ?

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

pwalter83 (6/20/2014)
Sachin Nandanwar (6/20/2014)
I dont understand the problem here.Your query seems to be achieving it.
CREATE TABLE [dbo].[GL](
[Sequence] [int] NOT NULL,
[WK NO] [int] NULL,
[AC] [nvarchar](10) NULL,
[COMP] [nvarchar](10) NULL,
[USD_AMOUNT] [numeric](16, 2) NULL,
[SOA_TYPE] [nvarchar](20) NULL
) ON [PRIMARY]
insert into GL
values('1','9','16201','12003','100','Agent')
insert into GL
values('2','10','16204','12003','200','Freight')
insert into GL
values('3','11','16207','12003','150','Agent')
insert into GL
values('5','12','16208','12003','250','Costs')
insert into GL
values('6','13','16224','12003','400','Cost')
insert into GL
values('7','14','16295','12003','500','Freight')
insert into GL
values('8','15','16295','12003','540','Cost')
GO
SELECT
(SELECT SUM(YourSum) FROM
(SELECT SUM(CASE WHEN SOA_TYPE IN ('Agent','Freight','Cost') or AC = '16208'THEN USD_AMOUNT ELSE 0 END) YourSum
FROM GL b WHERE b.[WK NO] < = a.[WK NO])T) as [Running Total]
FROM GL a
GROUP BY a.[WK NO], a.COMP
ORDER BY a.[WK NO], a.COMP
No its not working, I just checked it. This is the resultset that I get:
WKNO AUSD FUSD CUST agent_.. WK BAL Running Total
9100.000.000.000.00100.001552722.28
100.00200.000.000.00200.006866522.21
11150.000.000.000.00150.008658921.50
120.000.000.00250.00250.00-2440896.12
130.000.00400.000.00400.00-3911241.30
140.00500.000.000.00500.004083099.88
150.000.00540.000.00540.009459770.45
The Running total is all incorrect. I dont why its showing values in millions and decimals. Do you have any idea ?
Thanks.

The code you posted earlier does not match the table and sample data you posted later.