running: Oracle XE 11
Hi All, Im using the PARTITION BY Analytical function to get my running total for a report. (in this case the bank balance)
SUM (bank_stmts.amount) OVER ( PARTITION BY bank_stmts.account_ID ORDER BY bank_stmts.tx_date, bank_stmts.id
I add a starting balance into the table at the beginning of the year, and it all runs fine when I select on the whole years records..
However I need to query a subset of the report, i.e. a single month, or a selection of records, rather than full year. But I still want to see the calculated total for the whole year on the report, not a running total for the subset of records. Im not sure how to achieve it because when I select just a subset, it only totals the subset..
I see that there are a number of clauses on the analytic function, in particular ROWS BETWEEN or RANGE, but my assumption is that these will still only work on the queried rows and not the whole table..
My thinking at the moment, is that Ill have to calculate and insert the total into the table when the row gets inserted, probably via a trigger. However I did want to keep away from doing so..
Kind Regards
Richard