Re: SQL problem using count and group by with date datatype

Try it this way:

Select to_char(register_date,'dd-mon-yy').count(register_date) from user_table
where (regiater_date-to_date('01-Aug-99','dd-mon-yy') > 0) group by to_char(regiater_date,'dd-mon-yy');

You did not have a group by function specified ( order by won't do it )

It is also necessary, I believe, to have the group by columns specified first in the select clause...

smceneaney_at_my-deja.com wrote:

>I want to count records in a table using group by on a date datatype.
>The problem is that I want to use ONLY the dd-mon-yy part of the date in
>the count function and group by function. I want to group by the day
>part of the date field and not day and time. The SQL query below fails
>
>select count(register_date), to_char(register_date, 'dd-mon-yy')
>from user_table
>where (register_date - to_date('01-Aug-99', 'dd-mon-yy') > 0)
>order by to_char(register_date, 'dd-mon-yy')
>
>with the following error
>
>ERROR at line 1:
>ORA-00937: not a single-group group function
>
>The result I want to get is something like this
>
>5 1-AUG-99
>7 2-AUG-99
>99 4-AUG-99
>100 9-AUG-99
>150 12-AUG-99
>8 13-AUG-99
>89 21-AUG-99
>
>
>Can anybody tell me what I'm doing wrong??
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------    http://www.newsfeeds.com The Largest Usenet Servers in the World! ------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==----- Received on Tue Sep 07 1999 - 11:21:44 CDT