Running totals page 1
The correlated subquery may be used for calculation accumulation running totals — the usual practical problem.
In assumption of some rows` order the running total for each row is a sum of values for some numeric column in this row and for all rows above.
In the other words, the running total for first row in the ordered set would be equal to the numeric value in this row. For any other rows the running total would be equal to sum of numeric value in this row and the running total in the previous rows.
Let`s consider task like this.
For point 2 in the Outcome_o table for each day get the summary outlay for this day and for all previous days.
Here is the query outputs information about outlays in point 2 in order of ascending of the date:
Actually, to solve this problem we need to add one column for accumulating running total (run_tot). Correspondingly the theme, this column would be represent the correlated sub-query. In this subquery the total sum of the values in the out column would be calculated for this date and for all preceding dates.
In fact, the usage of point 2 is dictated by wish to reduce the resulting set. If we want to get running totals for all points in the Outcome_o table, we need to close in comment this string
And if we want to get running total for whole table we need to put off the condition for point`s equality:
But in this case we would get one total for different points which working in the same day. Here`s the such fragment from the resulting set:
This is not an obstacle if we understand what we want to get. If we want to get the accumulation of the outlay by days we need to exclude the point and summarize the outlay by days.
In the other case, we need to assign the order of the addition of the points` outlays if the dates is coincided.