Home » Developer & Programmer » Reports & Discoverer » Counter
Counter [message #268622] Wed, 19 September 2007 03:21 Go to next message
dimis283
Messages: 38
Registered: July 2007
Location: greece
Member
I have a repeating frame, for example
name  surname
dimis   Miles
pavlos  Kramnik 

and I want to add a field that gives the count of each line
as
count   name  surname
 1      dimis   Miles
 2      pavlos  Kramnik
 3      nikos   karpow

How I can do it?
Dimis
Re: Counter [message #268628 is a reply to message #268622] Wed, 19 September 2007 03:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One way would be to include the ROWNUM into a SELECT statement:
SQL> with test as
  2    (select 'dimis' name from dual
  3     union
  4     select 'pavlos' name from dual
  5     union
  6     select 'nikos' name from dual
  7    )
  8  select rownum, name from test
  9  order by name;

    ROWNUM NAME
---------- ------
         1 dimis
         2 nikos
         3 pavlos

SQL>

Another one is to create a summary column whose function is to count records.
Re: Counter [message #268729 is a reply to message #268628] Wed, 19 September 2007 08:36 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

Nothing much to do.
Just add a Summary column in your query group and use count function of the summary column.
For details show the attached example.
It will run on HR schema.

Cheers
Soumen
Re: Counter [message #268794 is a reply to message #268628] Wed, 19 September 2007 14:04 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, yet another possibility:
SQL> WITH test AS
  2        (SELECT 'dimis' name FROM dual
  3         UNION
  4         SELECT 'pavlos' name FROM dual
  5         UNION
  6         SELECT 'nikos' name FROM dual
  7        )
  8  SELECT row_number() over (ORDER BY name) rn, name
  9  FROM test;

        RN NAME
---------- ------
         1 dimis
         2 nikos
         3 pavlos
Previous Topic: Totals overlap
Next Topic: Custom Function returns REP-1401: 'cf_1' : Fatal PL/SQL error occurred
Goto Forum:
  


Current Time: Sat Dec 10 18:19:52 CST 2016

Total time taken to generate the page: 0.09787 seconds