StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Do you really need the extra table?</p> <p>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.</p> <p>This will get you the data you are looking for:</p> <pre><code>select account, bookdate, amount, sum(amount) over (partition by account order by bookdate) running_total from t / </code></pre> <p>This will create a view to show you the data as if it were a table:</p> <pre><code>create or replace view t2 as select account, bookdate, amount, sum(amount) over (partition by account order by bookdate) running_total from t / </code></pre> <p>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.</p> <p>Test data I used is:</p> <pre><code>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; </code></pre> <p>edit:</p> <p>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).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload