Re: group by week

From: T. Ravindran <ravi_at_oil.asm.nic.in>
Date: Wed, 6 Dec 2000 15:55:32 +0530
Message-Id: <10702.123768@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0038_01C05F9C.F6DD2980 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

Try

select count(status) from AAA
group by to_char(reg_date,'IW');

Ravindran.

Original Message -----=20
From: andrey=20
To: Multiple recipients of list ORACLE-L=20
Sent: Tuesday, December 05, 2000 11:50 PM
Subject: group by week

Dear list !
=20
I have a table AAA with fields (registration_date , status ) .
I need a report which will group the results by week .
=20
I.e instead of=20
select to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')), =
count(status)=20

from AAA
group by to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY'));
=20
=20
I need something like=20
select first_date_in_the_week(REGISTRATION_DATE) , count(status)=20
from AAA
group by week_of ( REGISTRATION_DATE ) ;
=20
=20
Any ideas ?

------=_NextPart_000_0038_01C05F9C.F6DD2980 Content-Type: text/html;

charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content=3D"text/html; charset=3Diso-8859-1" =

http-equiv=3DContent-Type>

<META content=3D"MSHTML 5.00.2614.3500" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Try</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>select count(status) from AAA<BR>group =

by=20
to_char(reg_date,'IW');</FONT></DIV>

<DIV>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Ravindran.</FONT></DIV></FONT></DIV>
<BLOCKQUOTE=20

style=3D"BORDER-LEFT: #000000 2px solid; MARGIN-LEFT: 5px; MARGIN-RIGHT: = 0px; PADDING-LEFT: 5px; PADDING-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A href=3D"mailto:bronfin_at_visualtop.com" =
title=3Dbronfin_at_visualtop.com>andrey</A>=20
</DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A =
href=3D"mailto:ORACLE-L_at_fatcity.com"=20
title=3DORACLE-L_at_fatcity.com>Multiple recipients of list ORACLE-L</A> =
</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Tuesday, December 05, =
2000 11:50=20
PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> group by week</DIV>
<DIV><BR></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>Dear =
list=20
!</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I =
have a table AAA=20
with fields (registration_date , status ) .</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I =
need a report=20
which will group the results by week .</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I.e =
instead of=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000>   =20
select to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY')), =
count(status)=20
</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000>   =20
from AAA</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000>   group=20
by =

to_date(to_char(REGISTRATION_DATE,'DD-MON-YYYY'));</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>I =
need something=20
like </SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000> =20
select  first_date_in_the_week(REGISTRATION_DATE) , count(status) =

<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000>   =20
from AAA</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN =
class=3D798181618-05122000>   group=20
by week_of ( </SPAN></FONT></SPAN></FONT><FONT face=3DArial=20
size=3D2><SPAN class=3D798181618-05122000>  REGISTRATION_DATE )=20
;</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D798181618-05122000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D798181618-05122000>Any =
Received on Wed Dec 06 2000 - 04:25:32 CST