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.
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 Sum(Amount) 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.