From: carl.pedersen@dartmouth.edu (L. Carl Pedersen)
Newsgroups: comp.databases.oracle
Subject: Weird GROUP BY/TO_CHAR(date) Interaction?
Date: 11 Sep 92 16:28:59 GMT
Followup-To: comp.databases.oracle
Organization: Dartmouth College

Can anyone explain the result of the middle select, below?

SQL> select dtgrd, count(*) from lmasv where dtgrd >= '01-JAN-91' group by
dtgrd;

DTGRD       COUNT(*)
--------- ----------
01-FEB-91          4
01-MAR-91         47
01-APR-91          1
01-MAY-91          7
01-JUN-91        541
01-AUG-91         42
01-DEC-91         37
01-MAR-92         51
01-MAY-92          3
01-JUN-92        590
01-JUN-93          1

11 rows selected.

SQL> select to_char(dtgrd), count(*) from lmasv where dtgrd >= '01-JAN-91'
group by dtgrd;

TO_CHAR(DTGRD)                                                             
  COUNT(*)
---------------------------------------------------------------------------
----------
01-JUN-83                                                                  
         4
01-JUN-83                                                                  
        47
01-JUN-83                                                                  
         1
01-JUN-83                                                                  
         7
01-JUN-83                                                                  
       541
01-JUN-83                                                                  
        42
01-JUN-83                                                                  
        37
01-JUN-83                                                                  
        51
01-JUN-83                                                                  
         3
01-JUN-83                                                                  
       590
01-JUN-83                                                                  
         1

11 rows selected.

SQL> select to_char(dtgrd), count(*) from lmasv where dtgrd >= '01-JAN-91'
group by to_char(dtgrd);

TO_CHAR(DTGRD)                                                             
  COUNT(*)
---------------------------------------------------------------------------
----------
01-APR-91                                                                  
         1
01-AUG-91                                                                  
        42
01-DEC-91                                                                  
        37
01-FEB-91                                                                  
         4
01-JUN-91                                                                  
       541
01-JUN-92                                                                  
       590
01-JUN-93                                                                  
         1
01-MAR-91                                                                  
        47
01-MAR-92                                                                  
        51
01-MAY-91                                                                  
         7
01-MAY-92                                                                  
         3

11 rows selected.

SQL>

Apologies for lines that are wrapped funny.  The question is, where the
heck did it get the values for the first column in the second query?? 
Aside from the order change, shouldn't the 2nd & 3rd queries have given the
same result?  (None of these dates have times.)

I don't seem to have any COLUMN or BREAK commands that are affecting this,
but even if I did, what could cause this?

Is this a bug?  In the kernel?  SQL*Plus?