On Dec 12, 5:45 am, Rem-8 <[email protected]> wrote:
> Hello. I'm in the middle of a problem.
> I have many many rows in a table. I need to add another column to it
> which has to be subtract of value A from row N and value B from row
> N-1. For example
>
> . NEW COLUMN
> 2008-10-10 12:05 223
> 2008-10-10 12:10 226 3
> 2008-10-10 12:15 238 12
>
> Etc... How this can be acomplished? Thanks in advance.
Use the LAG function:
SQL> create table data(
2 cr_dt date,
3 cum_sales number
4 );
Table created.
SQL>
SQL> insert all
2 into data
3 values(to_date('2008-10-10 12:05', 'RRRR-MM-DD HH24:MI'),223)
4 into data
5 values(to_date('2008-10-10 12:10', 'RRRR-MM-DD HH24:MI'),226)
6 into data
7 values(to_date('2008-10-10 12:15', 'RRRR-MM-DD HH24:MI'),238)
8 into data
9 values(to_date('2008-10-10 12:25', 'RRRR-MM-DD HH24:MI'),258)
10 into data
11 values(to_date('2008-10-10 12:45', 'RRRR-MM-DD HH24:MI'),287)
12 into data
13 values(to_date('2008-10-10 12:55', 'RRRR-MM-DD HH24:MI'),341)
14 select * From dual;
6 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select cr_dt, cum_sales, cum_sales-lag(cum_sales) over (order by
cr_dt) incr_sales
2 from data;
CR_DT CUM_SALES INCR_SALES
--------- ---------- ----------
10-OCT-08 223
10-OCT-08 226 3
10-OCT-08 238 12
10-OCT-08 258 20
10-OCT-08 287 29
10-OCT-08 341 54
6 rows selected.
SQL>
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
