SQL Server Sub Queries - How to use them effectively for calculated result columns?

Hi,

I'm trying to optimize a query which returns the sum of various values grouped by date range, as well as the accumulated value to that point.

For example

Month        Amount   Accumulated Amount
Jan 2011       $400           $400
Feb 2011      $300          $700
Mar 2011      $500         $1200

In the example about, the amount column is an aggregate of a number of rows grouped by date, and the accumulated amount is a sum of all rows. This accumulated amount is returned by a subquery.

My query at the moment does something like the following (extremely simplified):

Select
Month,
Sum(Amount),
(Select Sum(Amount) from Table)

from Table
Group by Month

My question is - is there any way to get around this subquery? I've tried taking it out and performing an update statement on the result set, but this did not improve performance.

Any advice you guys could provide is most appreciated.

Cheers,
Ben


AJS_DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

skrgaCommented:
I don't thinkyou can do it without subquery, maybe JOIN operator can fit your situation..

SELECT
 Month,
 sum(Amount),
 b. [Accumulated Amount]
FROM Table a
LEFT JOIN (select sum(Amount) as [Accumulated Amount] from Table) b on a.Date = b.Date 'Join columns are prabobly different in your case
group by a.Month
0
dqmqCommented:
You not only need a sub-query, it must be a correlated sub-query and there must be an appropriate ORDER BY clause.  I know this too is over-simplified, but the form goes like this:

Select
Month,
Sum(Amount),
(Select Sum(Amount) from Table T2 Where T2.Month <= T1.Month)
from Table T1
Group by T1.Month
Order By T1.Month
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TempDBACommented:
See this example. Modify it according to your requirement.

CREATE TABLE #abc(Id int, Amount INT)
GO
INSERT INTO #abc(Id, Amount)
VALUES
('1',200),
('1',150),
('1',50),
('2',75),
('2',25),
('2',200),
('3',100),
('3',400),
('4',900)


 ;WITH CTE AS  
(  
SELECT Id, SUM(Amount) AS y  
FROM #abc  
GROUP BY Id
)  
SELECT Id,y, y + COALESCE((SELECT SUM(y) FROM CTE b WHERE b.Id < a.Id),0) AS CumulativeAmount  
FROM CTE a  
ORDER BY 1
0
AmmarRCommented:
Dear AJS_Developer

what you are trying to achieve is called "calculate running totals"
and to achieve that in SQL server is not a simple task if you dont want to use nested select.

you have this article which explains the issue in simple words and provide a 4 methods to solve your problem

http://geekswithblogs.net/Rhames/archive/2008/10/28/calculating-running-totals-in-sql-server-2005---the-optimal.aspx

you have this article which is a bit more complicated but explains better how things work with calculating running totals and suggest how its achieved with sql 2012 without all the hasle.

if you want to go really deep and understand the ins and outs or what goes behind in calculating running totals i would suggest reading this article

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


but to get your job done think the you can use dqmq: code or a Self join method like below
SELECT a.Month,a.Amount,SUM(b.Amount)
FROM Table a
INNER JOIN Table b
ON (b.Month<= a.Month)
GROUP BY a.Month,a.Amount
ORDER BY a.Month,a.Amount

Open in new window

0
AmmarRCommented:
sorry i forgot to put the link of the second article

you have this article which is a bit more complicated but explains better how things work with calculating running totals and suggest how its achieved with sql 2012 without all the hasle.

http://www.codeproject.com/KB/database/RunningTotalWithSQLServer.aspx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.