Results 1 to 8 of 8
  1. #1
    Join Date
    May 2006
    Posts
    46

    Unanswered: Cross numeric columns addition query in oracle

    Hi Experts,

    Please help me in getting the value of a numeric column which will be the sum of the other numeric columns.
    For instance, my data sheet looks like this:

    col1(jobs) col2(completed) col3(jobs pending,ie col1-col2)

    4 2 2
    3 1 2+3-1 (ie previous row col3+current col1-
    current col2)

    Please give me a SQL query which will fetch col3 values in this logic.

    Thanks in advance
    PS

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This can be done with analytic functions:
    Code:
    SQL> create table t1 (id integer primary key, col1 integer, col2 integer);
    
    Table created.
    
    SQL> insert into t1 (id, col1, col2) values (1, 4, 2);
    
    1 row created.
    
    SQL> insert into t1 (id, col1, col2) values (2, 3, 1);
    
    1 row created.
    
    SQL> select col1, col2, sum(col1-col2) over (order by id) col3
      2  from t1
      3  /
    
          COL1       COL2       COL3
    ---------- ---------- ----------
             4          2          2
             3          1          4
    I added column ID because you need a column to define the order in which the rows are to be processed.

  3. #3
    Join Date
    Mar 2008
    Posts
    89
    I can understand that:
    col1(jobs) col2(completed) col3(jobs pending,ie col1-col2)
    Simple enough.

    But this
    4 2 2
    3 1 2+3-1 (ie previous row col3+current col1-
    current col2)
    as confusing as it is, seems to indicate that you mean something else.

    Do you really want to add the previous row's value to your current row calculation?
    In that case you may need to use PL/SQL, in order to be able to work with rows one by one, using a cursor loop.

    But please... clarify what exactly your desired output should be, with a clear example.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  4. #4
    Join Date
    Mar 2008
    Posts
    89
    Quote Originally Posted by andrewst
    This can be done with analytic functions:
    Code:
    SQL> create table t1 (id integer primary key, col1 integer, col2 integer);
    
    Table created.
    
    SQL> insert into t1 (id, col1, col2) values (1, 4, 2);
    
    1 row created.
    
    SQL> insert into t1 (id, col1, col2) values (2, 3, 1);
    
    1 row created.
    
    SQL> select col1, col2, sum(col1-col2) over (order by id) col3
      2  from t1
      3  /
    
          COL1       COL2       COL3
    ---------- ---------- ----------
             4          2          2
             3          1          4
    I added column ID because you need a column to define the order in which the rows are to be processed.
    I started replying and by the time I was done yours was already there.
    And apparently you understood better what he wanted.
    "My brain is just no good at being a relational Database - my relations suck real bad!"

  5. #5
    Join Date
    Jan 2009
    Location
    Dhaka, Bangladesh
    Posts
    51
    Quote Originally Posted by sathidevi
    Hi Experts,

    Please help me in getting the value of a numeric column which will be the sum of the other numeric columns.
    For instance, my data sheet looks like this:

    col1(jobs) col2(completed) col3(jobs pending,ie col1-col2)

    4 2 2
    3 1 2+3-1 (ie previous row col3+current col1-
    current col2)

    Please give me a SQL query which will fetch col3 values in this logic.

    Thanks in advance
    PS
    if you realy what to achieve 'previous row col3+current col1-current col2' , you should use PL/SQL instead of SQL
    Mohammad Hasan Shaharear
    E-mail
    Blog: http://shaharear.blogspot.com

  6. #6
    Join Date
    May 2006
    Posts
    46
    Thanks a lot, I got the correct SQL which solved my problem.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by hasan_uiu
    if you realy what to achieve 'previous row col3+current col1-current col2' , you should use PL/SQL instead of SQL
    Wrong! As Oracle offers LAG (and/or LEAD) functions, you can deal with previous (or next) rows' values in pure SQL - no PL/SQL is needed.

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by Littlefoot
    Wrong! As Oracle offers LAG (and/or LEAD) functions, you can deal with previous (or next) rows' values in pure SQL - no PL/SQL is needed.
    Analytical functions are the best thing since sliced bread
    It's really cool that Postgres will support the same rich set of analytical functions as Oracle with the next release.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •