Re: Row count in group

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 20 Aug 1999 11:51:08 GMT
Message-ID: <7pjffc$s4j$8_at_news.seed.net.tw>


Agi <agichen_at_my-deja.com> wrote in message news:7pgis7$hff$1_at_nnrp1.deja.com...
> Hi,everyone,
> I have a problem about row counter.
> If I have a table likes follows
>
> dept_id emp_id name
> ------- ------ -------
> 101 1 Mary
> 101 12 John
> 101 33 Tom
> 102 24 Jim
> 102 25 Peter
> ......
> 109 300 King
>
> Is it possible to get the result via SQL*PLUS ??
>
>
> dept rowcnt emp_id name
> 101 1 1 Mary
> 2 12 John
> 3 33 Tom
>
> 102 1 24 Jim --row# reset on dept
> 2 25 Peter
> ....
>
> I knew it's easy without row# shown,but how to add row counter ??
>
> Is it possible in sqlplus ??
> Any idea ??
>
> Rgds,
> Agi

It's possible.
See the following sample:

SQL> break on deptno
SQL> select a.deptno, count(*) as rowcnt, a.empno, a.ename   2 from emp a, emp b
  3 where a.deptno=b.deptno
  4 and a.empno>=b.empno
  5 group by a.deptno, a.empno, a.ename;

   DEPTNO ROWCNT EMPNO ENAME
--------- --------- --------- ----------

       10         1      7782 CLARK
                  2      7839 KING
                  3      7934 MILLER
       20         1      7369 SMITH
                  2      7566 JONES
                  3      7788 SCOTT
                  4      7876 ADAMS
                  5      7902 FORD
       30         1      7499 ALLEN
                  2      7521 WARD
                  3      7654 MARTIN
                  4      7698 BLAKE
                  5      7844 TURNER
                  6      7900 JAMES

14 rows selected. Received on Fri Aug 20 1999 - 13:51:08 CEST

Original text of this message