RE: sqlplus

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 14 Mar 2001 17:22:47 -0800
Message-ID: <F001.002CD252.20010314164115@fatcity.com>

> -----Original Message-----
> From: Ravindra Basavaraja [mailto:ravindra_at_sentica.com]
> Sent: mercredi, 14. mars 2001 16:11
>
> ...
> EX:The requirement is like say in a table with phone call 
> info we want to
> display the count of call made b/w two dates in a month and grouped by
> date.

If "by date" you mean a count for each 24-hour day period, you can use the trunc function. e.g. SQL> run
  1  select to_char (trunc (last_ddl_time), 'SYYYY/MM/DD'), count (*)
  2  from dba_objects
  3* group by trunc (last_ddl_time)

TO_CHAR(TRU   COUNT(*)
----------- ----------
 2001/01/02      21547
 2001/01/03          9
 2001/01/04        226
 2001/01/05          1
 2001/01/23          6
 2001/01/24          1
 2001/01/25         14
 2001/02/01          2
 2001/02/16        671
 2001/02/23          7
 2001/03/01         17
 2001/03/08        510
 2001/03/09          1

To get totals by month:

SQL> select to_char (trunc (last_ddl_time, 'MM'), 'SYYYY/MM'), count (*)
  2  from dba_objects
  3  group by trunc (last_ddl_time, 'MM') ;

TO_CHAR(   COUNT(*)
-------- ----------
 2001/01      21804
 2001/02        680
 2001/03        528

See the SQL Reference manual for documentation on the trunc function as it relates to dates. Obviously you can add a where clause to choose for a particular time frame, e.g. to select all dates for the month of March 2001, use the following where clause

select ...
from ...
where date_field >= to_date ('20010301', 'YYYYMMDD')
      and date_field < to_date ('20010401', 'YYYYMMDD')

---

any ignorant comments made are the sole responsibility of J. R. Kilchoer and should not reflect adversely upon my employer.

 

Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com Received on Wed Mar 14 2001 - 19:22:47 CST