Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: group by week

RE: group by week

From: andrey <bronfin_at_visualtop.com>
Date: Tue, 5 Dec 2000 21:25:20 +0200
Message-Id: <10701.123709@fatcity.com>


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 ?



Think you know someone who can answer the above question? Forward it to them!
to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Received on Tue Dec 05 2000 - 13:25:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US