June 20, 2014 at 3:53 am
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
June 20, 2014 at 4:01 am
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
June 20, 2014 at 4:12 am
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).
June 20, 2014 at 4:25 am
Quote strange.Can you post the entire query.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 20, 2014 at 4:31 am
Sachin Nandanwar (6/20/2014)
Quote strange.Can you post the entire query.
Please find the query attached.
Thanks.
June 20, 2014 at 4:48 am
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.
😎
June 20, 2014 at 5:47 am
Yes that's right..
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 20, 2014 at 6:40 am
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.
June 20, 2014 at 6:55 am
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
June 20, 2014 at 7:05 am
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) YourSumFROM 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.
June 20, 2014 at 7:13 am
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) YourSumFROM 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.
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
June 20, 2014 at 9:24 am
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) YourSumFROM 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.
June 20, 2014 at 9:46 am
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
June 20, 2014 at 10:03 am
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.
June 20, 2014 at 11:24 am
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.
Viewing 15 posts - 1 through 15 (of 75 total)
You must be logged in to reply to this topic. Login to reply