| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: group by week
Glen , thank U very much for your help .
I just think that i have to use to_date(to_char( , since the actual date
contains hours , mins , secs in it , so the "group by" clause will not help
here , since each actual date is different .
to_char needed to "truncate" the date to DD-MON-YYYY format ( i.e. to get
rid of the minutes , seconds etc ) .
to_date needed for "order by" , cause i want to order dates as dates and not
as strings ( string '01-DEC-2000' comes earlier than string '02-NOV-1970' )
.
Thanks a lot .
-----Original Message-----
From: Shaw, Glen [mailto:Glen.Shaw_at_BellSouth.com]
Sent: Tue, December 05, 2000 9:07 PM
To: 'andrey'; oralist_at_lists; ORACLE-L_at_IC.SUNYSB.EDU;
ORACLE-L_at_fatcity.com; oracledba_at_quickdoc.co.uk; oracledba_at_lazydba.com
Subject: RE: group by week
Andrey,
Assuming the registration_date field is already a date field, you don't need to do the to_date(to_char( process. Next assumption is that you want Sunday to be day 1 of the week. If you want to start on Monday, just add 2 instead of 1. That being said, the following group by should work for you.
select trunc(registration_date, 'WW')+1 REG_DATE, count(status)
from AAA
group by trunc(registration_date, 'WW')+1;
Hope this helps,
Glen
-----Original Message-----
From: andrey [mailto:bronfin_at_visualtop.com]
Sent: Tuesday, December 05, 2000 1:23 PM
To: oralist_at_lists; ORACLE-L_at_IC.SUNYSB.EDU; ORACLE-L_at_fatcity.com;
oracledba_at_quickdoc.co.uk; oracledba_at_lazydba.com
Subject: group by week
Dear list !
I have a table AAA with fields (registration_date , status ) . I need a report which will group the results by week .
I.e instead of
select to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')), count(status)
from AAA
group by to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY'));
I need something like
select first_date_in_the_week(REGISTRATION_DATE) , count(status)
from AAA
group by week_of ( REGISTRATION_DATE ) ;
Any ideas ?
 |
 |