Re: Counting rows

Here's an ugly solution using analytic functions. I'm sure a better solution will be forthcoming

SQL> CREATE TABLE TAB(DATCOL DATE); Table created.

SQL> BEGIN
  2 FOR I IN 1..365 LOOP
  3 INSERT INTO TAB VALUES (SYSDATE + i);   4 LOOP;
  5 END;
  6 /
LOOP;
    *
ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00103: Encountered the symbol ";" when expecting one of the following: begin case declare exit for goto if loop mod null pragma raise return select update while with <an identifier> <a double-quoted delimited-identifier> <a bind variable> << close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge <a single-quoted SQL string> pipe

SQL> ED
Wrote file afiedt.buf

  1 BEGIN
  2 FOR I IN 1..365 LOOP
  3 INSERT INTO TAB VALUES (SYSDATE + i);   4 END LOOP;
  5* END;
SQL> / PL/SQL procedure successfully completed.

SQL> GET S
  1 SELECT MONTH,MAX(CNT) TOTAL
  2 FROM(
  3 SELECT TRUNC(DATCOL,'MONTH') AS MONTH,COUNT(*) OVER (ORDER BY DATCOL) CNT
  4 FROM TAB)
  5* GROUP BY MONTH
SQL> / MONTH TOTAL

--------- ----------
01-SEP-02         26
01-OCT-02         57
01-NOV-02         87
01-DEC-02        118
01-JAN-03        149
01-FEB-03        177
01-MAR-03        208
01-APR-03        238
01-MAY-03        269
01-JUN-03        299
01-JUL-03        330

MONTH          TOTAL
--------- ----------
01-AUG-03        361
01-SEP-03        365

13 rows selected.

SQL>

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
******************************************
"Clemens Pichl" <clemens.pichl_at_schering.de> wrote in message
news:Xns927F9FA8B16A6cpichl_at_130.149.4.16...


> Hi,

>


> For some statistics, I would like to generate a report with the number of

> entries in a table that have been added until a the end of each month.

> The number should always count from the beginning.

>


> I tried the following:

> select trunc(date_column, 'MONTH'), count(*)

> from tab

> group by trunc(date_column, 'MONTH')

>


> This is not sufficient, because the counts are not cumulative.

>


> Any suggestion?

>


> Thnx in advance,

> Clemens