Thanks, thats an interesting example. Just out of curiosity why do you calculate such totals from an accounts-receivable table? Wouldn't it be easier to have a seperate totals table and update it every time a new record was added? Either in a transction explicitly do two updates, or use a trigger on the accounts-receivable table that does it automatically....

I wasn't part of the design decision for that, so I can't give a good answer for why it wasn't done that way.

However I can think of at least two reasons why it wasn't done that way.

The first is because orders are generated in one database, and then accounts receivables are dealt with in another. You really want those two to have limited communication - that way if the front end database is compromised your financials information is still safe. However that design makes your suggested interface much more difficult. (Not impossible, I can think of workarounds, but more difficult.)

The second is because the people building the system were building the transactional system first, and then the reporting needs came after. At the time that the transactional system was designed and built, the need wasn't obvious. At the time some reporting needed to be built it was easier to deal with what was rather than to insist on redesigning what was already out there and working. After all we do succeed in running the requested query...

Yes, I know how to do it conceptually in pure SQL. I also know how to do it reasonably quickly using Oracle's analytic extensions. I prefer the latter solution since it runs at an acceptable speed without having a DBA get involved in changing the schema.