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

A copy of this was sent to smceneaney_at_my-deja.com (if that email address didn't require changing) On Tue, 07 Sep 1999 15:24:27 GMT, you 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')
>

select count(register_date), trunc( register_date ) from user_table
where register_date >= to_date( '01-AUG-99' ) GROUP BY trunc( register_date )
order by trunc( register_date )

you did not have a GROUP BY in there at all -- that was the error.

Also recommend you use TRUNC() on the date to get rid of the time component -- not a to_char. order by to_char(dt) would do an ascii sort so that 01-AUG-99 would sort before 01-JAN-99 for example.

also recommend you use the predicate "where dt >= dt2" instead of "where dt-dt2
> 0" as the first will allow the use of an index and the second will not (unless
you index that funcion in Oracle8i, 8.1 that is).

>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.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 07 1999 - 10:55:04 CDT