Running total by grouped records in table

Question

I have a table like this (Oracle, 10)

Account     Bookdate     Amount
      1     20080101        100
      1     20080102        101
      2     20080102        200
      1     20080103       -200
...

What I need is new table grouped by Account order by Account asc and Bookdate asc with a running total field, like this:

Account     Bookdate     Amount     Running_total
      1     20080101        100               100
      1     20080102        101               201
      1     20080103       -200                 1
      2     20080102        200               200
...

Is there a simple way to do it?

Thanks in advance.

Solution 1

Do you really need the extra table?

You can get that data you need with a simple query, which you can obviously create as a view if you want it to appear like a table.

This will get you the data you are looking for:

select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total
from t
/

This will create a view to show you the data as if it were a table:

create or replace view t2
as
select 
    account, bookdate, amount, 
    sum(amount) over (partition by account order by bookdate) running_total 
from t
/

If you really need the table, do you mean that you need it constantly updated? or just a one off? Obviously if it's a one off you can just "create table as select" using the above query.

Test data I used is:

create table t(account number, bookdate date, amount number);

insert into t(account, bookdate, amount) values (1, to_date('20080101', 'yyyymmdd'), 100);

insert into t(account, bookdate, amount) values (1, to_date('20080102', 'yyyymmdd'), 101);

insert into t(account, bookdate, amount) values (1, to_date('20080103', 'yyyymmdd'), -200);

insert into t(account, bookdate, amount) values (2, to_date('20080102', 'yyyymmdd'), 200);

commit;

edit:

forgot to add; you specified that you wanted the table to be ordered - this doesn't really make sense, and makes me think that you really mean that you wanted the query/view - ordering is a result of the query you execute, not something that's inherant in the table (ignoring Index Organised Tables and the like).

Solution 2

I'll start with this very important caveate: do NOT create a table to hold this data. When you do you will find that you need to maintain it which will become a never ending headache. Write a view to return the extra column if you want to do that. If you're working with a data warehouse then maybe you would do something like this, but even then err on the side of a view unless you simply can't get the performance that you need with indexes,decent hardware, etc.

Here's a query that will return the rows the way that you need them.

SELECT
    Account,
    Bookdate,
    Amount,
    (
        SELECT SUM(Amount)
        FROM My_Table T2
        WHERE T2.Account = T1.Account
          AND T2.Bookdate <= T1.Bookdate
    ) AS Running_Total
FROM
    My_Table T1

Another possible solution is:

SELECT
    T1.Account,
    T1.Bookdate,
    T1.Amount,
    SUM(T2.Amount)
FROM
    My_Table T1
LEFT OUTER JOIN My_Table T2 ON
    T2.Account = T1.Account AND
    T2.Bookdate <= T1.Bookdate
GROUP BY
    T1.Account,
    T1.Bookdate,
    T1.Amount

Test them both for performance and see which works better for you. Also, I haven't thoroughly tested them beyond the example which you gave, so be sure to test some edge cases.

Solution 3

Use analytics, just like in your last question:

create table accounts
( account number(10)
, bookdate date 
, amount   number(10)
);

delete accounts;

insert into accounts values (1,to_date('20080101','yyyymmdd'),100);
insert into accounts values (1,to_date('20080102','yyyymmdd'),101);
insert into accounts values (2,to_date('20080102','yyyymmdd'),200);
insert into accounts values (1,to_date('20080103','yyyymmdd'),-200);

commit;

select account
,      bookdate 
,      amount
,      sum(amount) over (partition by account order by bookdate asc) running_total
from accounts
order by account,bookdate asc
/

output:

   ACCOUNT BOOKDATE     AMOUNT RUNNING_TOTAL
---------- -------- ---------- -------------
         1 01-01-08        100           100
         1 02-01-08        101           201
         1 03-01-08       -200             1
         2 02-01-08        200           200

Comments

[tuinstoel]

A table is indeed not needed, maybe a materialized view if the performance is very, very important.

[Zsolt Botykai]

I need the table for backup purposes. Thanks for your answer, I'll give it a try tomorrow.

[Zsolt Botykai]

I need the table for backup purposes. Thanks for your answer, I'll give it a try tomorrow. – Zsolt

[Zsolt Botykai]

I need the table for backup purposes. Thanks for your answer, I'll give it a try tomorrow. – Zsolt

[tuinstoel]

I believe that MySQL doesn't support analytics. I'm not sure. I don't know how to do a running total in MySQL.