Re: count(*) in sql statement

Try something like this:

1. convert to julian dates : to_date(adate, 'J')
2. mod the result to some time interval (like 30 days)
3. GROUP BY the result

OR
1) convert to the week/month/year of interest 2) GROUP BY that

SQL> select * from atab order by adate;  

ADATE

12-DEC-93
01-JAN-94
02-FEB-94
10-FEB-94
20-FEB-94
05-MAY-94
 

Example 1:
select to_date(trunc(to_char(adate,'J') / 30) * 30, 'J'), count(*) from atab
group by to_date(trunc(to_char(adate,'J') / 30) * 30, 'J')  

TO_DATE(T COUNT(*)

--------- ----------
28-NOV-93          1
28-DEC-93          1
27-JAN-94          3
27-APR-94          1

Example 2:

col month format a10
select TO_CHAR(ADATE,'MON-YYYY') Month, count(*) from atab
group by TO_CHAR(ADATE,'MON-YYYY')  

MONTH COUNT(*)

---------- ----------
DEC-1993            1
FEB-1994            3
JAN-1994            1
MAY-1994            1

In article <2s54bb$np7_at_hsc.usc.edu>, mcitron_at_hsc.usc.edu (Mark Citron) writes:
|> Is it possible to select two different counts in one statement? That is
|> I have a list of dates, can I select the number of dates between dateA and dateB
|> and also the number of dates between dateC and dateD? All on one line of output.
|>
|> Any suggestions would be appreciated.
|> Mark Citron
|> Childrens Hospital Los Angeles
|>
|> --
|> Mark Citron
|> mark_at_neurosci.usc.edu
|>
Received on Sat May 28 1994 - 01:06:21 CEST