Running total in sql and additional issue

Question

Post reply

Running total in sql and additional issue

pwalter83

SSChampion

Points: 14575
More actions
June 19, 2014 at 5:06 am

#294713

Hi,
I need to do a running total in sql and at the same time need to perform an aggregate function on an expression containing an aggregate or a subquery. This is where the problem gets even more tough.
Below is the code I have created to achieve this but I get an error - Cannot perform an aggregate function on an expression containing an aggregate or a subquery
(SELECT SUM(SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN [DEAL AMOUNT] ELSE 0 END) ) FROM GL b WHERE b.[COMP] <= a.[COMP])
The above is just part of the bigger select statement. Could somebody please point me to the right direction ?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

You cannot put a SUM inside a SUM.

If you would post the table DDL, some sample data and the desired output, it would be easier for people to help you.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 2

Something like this

SELECT SUM(YourSum) FROM

(

SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) +

SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) +

SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN [DEAL AMOUNT] ELSE 0 END)YourSum

FROM GL b WHERE b.[COMP] <= a.[COMP]

)T

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

pwalter83 SSChampion Points: 14575 More actions · Answer 3

Sachin Nandanwar (6/19/2014)
Something like this
SELECT SUM(YourSum) FROM
(
SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) +
SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) +
SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN [DEAL AMOUNT] ELSE 0 END)YourSum
FROM GL b WHERE b.[COMP] <= a.[COMP]
)T

Hi,

Thanks a lot for your reply. I tried to implement what you suggested but the running total for each row doesnt add up correctly.

I also tried the following but get an error - Incorrect syntax near 'order'.

SUM(USD_AMOUNT) OVER ( PARTITION BY comp ORDER BY GL.[WK NO]) AS [Running Total]

Could you please tell the reason for the error in the above code ?

Thanks.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 4

pwalter83 (6/19/2014)
Sachin Nandanwar (6/19/2014)
Something like this
SELECT SUM(YourSum) FROM
(
SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) +
SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) +
SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN [DEAL AMOUNT] ELSE 0 END)YourSum
FROM GL b WHERE b.[COMP] <= a.[COMP]
)T
Hi,
Thanks a lot for your reply. I tried to implement what you suggested but the running total for each row doesnt add up correctly.
I also tried the following but get an error - Incorrect syntax near 'order'.
SUM(USD_AMOUNT) OVER ( PARTITION BY comp ORDER BY GL.[WK NO]) AS [Running Total]
Could you please tell the reason for the error in the above code ?
Thanks.

Which version of SQL Server?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

pwalter83 (6/19/2014)
Sachin Nandanwar (6/19/2014)
Something like this
SELECT SUM(YourSum) FROM
(
SELECT SUM(CASE WHEN SOA_TYPE = 'Agent' THEN USD_AMOUNT ELSE 0 END) +
SUM(CASE WHEN SOA_TYPE = 'Freight' THEN USD_AMOUNT ELSE 0 END) +
SUM(CASE WHEN SOA_TYPE = 'Cost' THEN USD_AMOUNT ELSE 0 END) + SUM(CASE WHEN AC = '16208' THEN [DEAL AMOUNT] ELSE 0 END)YourSum
FROM GL b WHERE b.[COMP] <= a.[COMP]
)T
Hi,
Thanks a lot for your reply. I tried to implement what you suggested but the running total for each row doesnt add up correctly.
I also tried the following but get an error - Incorrect syntax near 'order'.
SUM(USD_AMOUNT) OVER ( PARTITION BY comp ORDER BY GL.[WK NO]) AS [Running Total]
Could you please tell the reason for the error in the above code ?
Thanks.

SUM() OVER(ORDER BY) was implemented in SQL Server 2012, it doesn't exist in SQL Server 2008 (this forum section) and will raise the error shown. What you are asking for isn't impossible in 2008, but it will require a properly constructed and representative sample data set for folks to code against.

^{“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

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 6

This is going to be terribly slow and resource intensive because it contains a "Triangular Join". Please see the following article on the subject. Even a cursor and While loop would be faster.

http://www.sqlservercentral.com/articles/T-SQL/61539/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pwalter83 SSChampion Points: 14575 More actions · Answer 7

No further posts to this thread, please see the new link:

http://www.sqlservercentral.com/Forums/Topic1584256-391-4.aspx

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

pwalter83 (6/23/2014)
No further posts to this thread, please see the new link:
http://www.sqlservercentral.com/Forums/Topic1584256-391-4.aspx

Actually, it's the original thread for this problem and, I agree... let's get back to the original thread. No more replies on this thread please. Consider it "dead". Thanks folks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)