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: SQL Code

Re: SQL Code

From: Oweson Flynn <Oweson.Flynn_at_liberty.co.za>
Date: Fri, 12 Jan 2001 10:39:27 +0200
Message-Id: <10739.126469@fatcity.com>


This is a multi-part message in MIME format.

------=_NextPart_000_0111_01C07C83.EFC37CE0
Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

SQL CodeHi Kevin,

What about something like=20
SELECT=20
   COUNT(*),=20
   Code,=20
   TO_CHAR( Date_Stamp, 'HH24' ) Hour
FROM
  Table_Name
GROUP BY
   Code,=20
   TO_CHAR( Date_Stamp, 'HH24' ) Hour;

It might take some processing to do the grouping by the To_Char (which give= s the hour), but it should work.

Regards
Oweson Flynn



Certified Oracle DBA
The Flynn Consultancy
Tel: 082-600-7-006
Fax: (011) 782-9313
EMail: oef_at_icon.co.za

  Hi All,=20

  I have an interesting problem, lets say I have a table with 2 fields, one=  with a code, and the other with a full datestamp, -+ 8 million records.

  What will the SQL code be to query this table, to show the result - the c= ount of all codes that occur between every hour for the day, ie

  5 counts of code type a, 6 of type b etc, between 01h00 and 02h00=20   7 counts of type a, 8 of b etc, between 02h00 and 03h00 etc etc etc=20

  Any ideas anybody ?=20
  Thanx=20
  Kevin N=20


  Disclaimer and confidentiality note

  Everything in this e-mail and any attachments relating to the official bu=
siness of Standard Bank Investment Corporation (Stanbic) is proprietary to =
the company. It is confidential, legally privileged and protected by law. S=
tanbic does not own and endorse any other content. Views and opinions are t=
hose of the sender unless clearly stated as being that of Stanbic.=20

  The person addressed in the e-mail is the sole authorised recipient. Plea= se notify the sender immediately if it has unintentionally reached you and = do not read, disclose or use the content in any way.

  Stanbic can not assure that the integrity of this communication has been = maintained nor that it is free of errors, virus, interception or interferen= ce.



This message may contain information which is confidential and subject to l=
egal privilege. If you are not the intended recipient, you may not peruse, =
use, disseminate, distribute or copy this message. If you have received thi=
s message in error, please notify the sender immediately by email, facsimil=
e or telephone and return and/or destroy the original message.

------=_NextPart_000_0111_01C07C83.EFC37CE0
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><TITLE>SQL Code</TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT size=3D2>Hi Kevin,</FONT></DIV>
<DIV><FONT size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT size=3D2>What about something like </FONT></DIV>
<DIV><FONT size=3D2>SELECT </FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; COUNT(*), </FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; Code, </FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; TO_CHAR( Date_Stamp, 'HH24' ) Hour</FONT><=
/DIV>
<DIV><FONT size=3D2>FROM</FONT></DIV>
<DIV><FONT size=3D2>&nbsp; Table_Name</FONT></DIV>
<DIV><FONT size=3D2>GROUP BY</FONT></DIV>
<DIV><FONT size=3D2>
<DIV><FONT size=3D2>&nbsp;&nbsp; Code, </FONT></DIV>
<DIV><FONT size=3D2>&nbsp;&nbsp; TO_CHAR( Date_Stamp, 'HH24' ) Hour;</FONT>=
</DIV>

<DIV>&nbsp;</DIV>
<DIV>It might take some processing to do the grouping by the To_Char (which= g
 ives the hour), but it should work.</DIV> <DIV>&nbsp;</DIV></FONT></DIV>
<DIV>Regards<BR>Oweson=20
Flynn<BR>------------------------------------------------------------------=
<BR>Certified=20
Oracle DBA<BR>The Flynn Consultancy<BR>Tel: 082-600-7-006<BR>Fax: (011)=20 782-9313<BR>EMail: <A href=3D"mailto:oef_at_icon.co.za">oef_at_icon.co.za</A></DI= V>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LE= FT: #000000 2px solid; MARGIN-RIGHT: 0px">   <DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>   <DIV=20
  style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>Fro= m:</B>=20
  <A title=3DKNaik_at_mail.sbic.co.za href=3D"mailto:KNaik_at_mail.sbic.co.za">Na= ik, Kevin=20
  K</A> </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>To:</B> <A title=3DORACLE-L_at_fatcity.co= m=20
  href=3D"mailto:ORACLE-L_at_fatcity.com">Multiple recipients of list ORACLE-L= </A>=20
  </DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Friday, January 12, 2001 8:5= 5=20
  AM</DIV>
  <DIV style=3D"FONT: 10pt arial"><B>Subject:</B> SQL Code</DIV>
  <DIV><FONT size=3D2></FONT><FONT size=3D2></FONT><BR></DIV>
  <P><FONT size=3D2>Hi All,</FONT> </P>
  <P><FONT size=3D2>I have an interesting problem, lets say I have a table =
with 2=20
  fields, one with a code, and the other with a full datestamp, -+ 8 millio= n=20
  records.</FONT></P>
  <P><FONT size=3D2>What will the SQL code be to query this table, to show = the=20
  result - the count of all codes that occur between every hour for the day= ,=20
  ie</FONT></P>
  <P><FONT size=3D2>5 counts of code type a, 6 of type b etc, between 01h00=  and=20
  02h00</FONT> <BR><FONT size=3D2>7 counts of type a, 8 of b etc, between 0= 2h00=20
  and 03h00 etc etc etc</FONT> </P>
  <P><FONT size=3D2>Any ideas anybody ?</FONT> <BR><FONT size=3D2>Thanx</FO= NT>=20
  <BR><FONT size=3D2>Kevin N</FONT> </P>
  <DIV>
  <DIR><FONT color=3D#0000ff size=3D1>
  <P>______________________________________________</FONT><FONT face=3DAria=
l=20
  size=3D1><B><FONT face=3DArial size=3D2></FONT></B></FONT></P>   <P><FONT face=3DArial size=3D1><B><FONT face=3DArial size=3D2>Disclaimer = and=20
  confidentiality note</B></FONT></FONT></P>   <P><FONT face=3DArial size=3D1><FONT face=3DArial size=3D2>Everything in = this e-mail=20
  and any attachments relating to the official business of Standard Bank=20   Investment Corporation</FONT><FONT face=3DArial color=3D#0000ff size=3D2>=  

  </FONT><FONT face=3DArial size=3D2>(Stanbic) is proprietary to the compan= y. It is=20
  confidential, legally privileged and protected by law. Stanbic</FONT><FON= T=20
  face=3DArial color=3D#0000ff size=3D2> </FONT><FONT face=3DArial size=3D2= >does not own=20
  and endorse any other content. Views and opinions are those of the sender=  

  unless clearly stated as being that of Stanbic. </FONT></FONT></P>   <P><FONT face=3DArial size=3D1><FONT face=3DArial size=3D2>The person add= ressed in the=20
  e-mail is the sole authorised recipient. Please notify the sender immedia= tely=20
  if it has unintentionally reached you and do not read, disclose or use th= e=20
  content in any way.</P>
  <P align=3Djustify></P>
  <P>Stanbic can not assure that the integrity of this communication has be= en=20
  maintained nor that it is free of errors, virus, interception or=20   interference.</P></FONT></FONT><FONT color=3D#0000ff></DIR>   <P><FONT=20
  size=3D1>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbs=

p;&nbsp;&nbsp;_______________________________________________</FONT></P></F=
ONT></DIV></BLOCKQUOTE><CODE><FONT SIZE=3D3><BR> <BR>
********************************************************************<BR>
<BR>
This message may contain information which is confidential and subject to l=
egal privilege. If you are not the intended recipient, you may not peruse, =
use, disseminate, distribute or copy this message. If you have received thi=
s message in error, please notify the sender immediately by email, facsimil=
e or telephone and return and/or destroy the original message.<BR> Received on Fri Jan 12 2001 - 02:39:27 CST

Original text of this message

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