DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Adding new column for Transaction counter from SQL query

  1. #1
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50

    Adding new column for Transaction counter from SQL query

    I have a table trans with all transations in it
    transid,prod,amt are the three columns

    select * from trans; is
    transid prod amt
    100 2 2.2
    100 4 3.2
    100 3 8.7
    100 3 6.5
    101 3 2.5
    101 14 18.1
    101 ... ...

    I want to add a column at the end of the result set such that i can have a counter for all lines of the trans number 100. like

    select * from trans where !!!! ;
    transid prod amt transln
    100 2 2.2 1
    100 4 3.2 2
    100 3 8.7 3
    100 3 6.5 4
    101 3 2.5 1
    101 14 18.1 2
    101 ... ... ...

    So how can i achieve this from one sql statement from the trans table , can i achieve this using some pseudo column or any other function available in Oracle. I don'nt wana use pl/sql procedure
    An elephant is a mouse with an operating system.

  2. #2
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Is this what you mean

    Code:
    SQL> l
      1  SELECT
      2    deptno,
      3    dname,
      4    (SELECT COUNT(*)
      5     FROM emp
      6     WHERE deptno = d.deptno) "No Employees"
      7* FROM dept d
    SQL> /
    
        DEPTNO DNAME          No Employees
    ---------- -------------- ------------
            10 ACCOUNTING                3
            20 RESEARCH                  5
            30 SALES                     6
            40 OPERATIONS                0
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  3. #3
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    Code:
    select transid, prod, amt, 
           row_number() over (partition by transid order by rowid) transln       
      from trans;
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  4. #4
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    I just want the trans line number at the end of each line which I don'nt have it in my current table.
    What you told me is the total count of the lines in each transaction.
    An elephant is a mouse with an operating system.

  5. #5
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    Thanks a lot Tommazz it worked!!!!
    An elephant is a mouse with an operating system.

Posting Permissions

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


Click Here to Expand Forum to Full Width