Re: trying to Ggroup by

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Fri, 02 Apr 2004 07:43:00 GMT
Message-ID: <8Q8bc.136164$Wa.33758@news-server.bigpond.net.au>

"Jawahar Rajan" <jrajan_at_nc.rr.com> wrote in message news:li4bc.3895$w84.618835_at_twister.southeast.rr.com...
> All,
> I have data of people's birth
> I am trying to group byno of births per week
> I have in a table ID, Birth_Date, Gender, initials, site
> I want to group by births per week per site
>
> Is there an Oracle function for grouping by Week
>
> Select Count(id),site
> From birth_Table
> group by birth_Date, site
>
> Do I need to get the difference betweeen the Max and min dates and then
> divide by 7 and see how manydate fall in each week category?
>
> ANy help is appreciated
> Jawahar
>
>

Jawahar,

You may want to look at the TRUNC function in association with one of the following date format models (see the SQL Reference Manual): (1) WW Same day of the week as the first day of the year (2) IW Same day of the week as the first day of the ISO year

My test run is as follows:

CREATE TABLE birth_table

   AS SELECT

            rownum id,
            MOD( rownum, 10) site,
            created birth_date
         FROM
            all_objects
         WHERE
            rownum < 100

/

SELECT

      COUNT( id ),
      site,
      TRUNC( birth_date, 'WW')
   FROM
      birth_table
   GROUP BY
      site,
      TRUNC( birth_date, 'WW')

;

 COUNT(ID) SITE TRUNC(BIR

---------- ---------- ---------
         9          0 07-MAY-02
        10          1 07-MAY-02
        10          2 07-MAY-02
        10          3 07-MAY-02
        10          4 07-MAY-02
        10          5 07-MAY-02
        10          6 07-MAY-02
        10          7 07-MAY-02
        10          8 07-MAY-02
        10          9 07-MAY-02

Douglas Hawthorne Received on Fri Apr 02 2004 - 01:43:00 CST