Re: group by month ?

<javanewbie_at_my-deja.com> wrote in message news:7v5p32$3bq$1_at_nnrp1.deja.com...
> i have a field that stores the date /time .
>
> how do i group the resultset by month ?
>
> meaning, it shows :-
>
> 1999 Jul count1
> Aug count2
> Sep
> Oct
>
>
> i heard that i need to use to_char(date) to convert the
> date/time to a char .
> then, i need to count the no of occurances of jan 1999, feb 1999, march
> 1999 and so on.
> Any idea how this could be done ?

No. It's better NOT use to_char(date) to perform a group operation. Since it causes overhead to perform many, many datatype conversions.

You should use

  select to_char(trunc(date_column, 'month'), 'YYYY Mon'), count(*)     from table_name
    group by trunc(date_column, 'month');

Here is a simple test. You can find the difference: Using to_char() to perform group operation is more slower, and returns in wrong (alphabet) order.

SQL> set timing on;
SQL> select to_char(trunc(prod_date, 'month'), 'YYYY Mon'), count(*)   2 from test
  3 group by trunc(prod_date, 'month');

TO_CHAR( COUNT(*)
-------- ---------

1999 Jul      8968
1999 Aug     62021
1999 Sep     60055
1999 Oct     43965

 real: 2844
SQL> select to_char(prod_date, 'YYYY Mon'), count(*)   2 from test
  3 group by to_char(prod_date, 'YYYY Mon');

TO_CHAR( COUNT(*)
-------- ---------

1999 Aug     62021
1999 Jul      8968
1999 Oct     43965
1999 Sep     60055

 real: 11456 Received on Thu Oct 28 1999 - 12:03:37 CDT