group by week

From: andrey <bronfin_at_visualtop.com>
Date: Tue, 5 Dec 2000 20:22:49 +0200
Message-Id: <10701.123696@fatcity.com>

This is a multi-part message in MIME format.

------=_NextPart_000_0000_01C05EF9.22DF48A0 Content-Type: text/plain;

charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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 ?

------=_NextPart_000_0000_01C05EF9.22DF48A0 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.2920.0" name=3DGENERATOR></HEAD>
<BODY>
<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>  =
select =20
first_date_in_the_week(REGISTRATION_DATE) , count(status)=20
<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 = size=3D2><SPAN=20
class=3D798181618-05122000>  REGISTRATION_DATE ) = ;</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 =
ideas=20
?</SPAN></FONT></DIV></DIV></BODY></HTML> Received on Tue Dec 05 2000 - 12:22:49 CST