Hi,
I found an interesting article on running totals at
http://www.databasejournal.com/features/mssql/article.php/3112381.
I have converted one to postgresql for my banking account:
-- Subtotals on categories and a GrandTotal
select *,
case when a.oid= (select oid from nchecks where a.category
=category order by category desc, oid desc limit 1)
then (select sum(amount)::text from nchecks where oid <= a.oid
and a.category=category)
else ' '
end as SubTotal,
case when a.oid = (select oid from nchecks order by category
desc, oid desc limit 1)
then (select sum(amount) from nchecks)::text
else ' '
end as GrandTotal
from nchecks a
order by category ,oid
The account has a 'category' for each transaction and of course an
'amount' for
the transaction ( and some other fields...)
The table does not have any fields defined as keys ( I am using oids
with there being
no chance for overflow...).
With only ~3300 rows the rascal takes its time...
explain begins with...
Sort (cost=626576.75..626584.96 rows=3283 width=181).....
Is there any way to get this puppy running a bit faster?
Jerry
