Re: Aggregate query

A copy of this was sent to "Alexander T .Agung" <alexander.agung_at_lippobank.co.id>
(if that email address didn't require changing) On Wed, 9 Feb 2000 13:48:40 +0700, you wrote:

>Hello all,
>
>I want to do a query to calculate aggregate using SUM function
>
>SELECT cdhsum.start_time, evsum.event, sum(evsum.sum_tot)
>FROM cdhsum, evsum
>WHERE cdsum.sumid = evsum.sumid AND
> (TO_CHAR(start_time, 'DD/MM/YY') BETWEEN '&date1' dan
>'&date2')
>GROUP BY start_time, event;
>
>result : 208 rows.
>
>but the query result was the same as i do the query without GROUP BY.
>
>SELECT cdhsum.start_time, evsum.event, evsum.sum_tot
>FROM cdhsum, evsum
>WHERE cdsum.sumid = evsum.sumid AND
> (TO_CHAR(start_time, 'DD/MM/YY') BETWEEN '&date1' dan
>'&date2');
>
>result : 208 rows
>
>What is wrong with the query ?????
>

nothing, you selected 208 distinct start_time, event pairs. None of them where equal -- so none of them 'grouped'.

btw: consider coding:

start_time between to_date( '&date1', 'dd/mm/RR' ) and

                  (to_date( '&date2', 'dd/mm/RR' ) + 86399/86400)

instead of

TO_CHAR(start_time, 'DD/MM/YY') BETWEEN '&date1' and '&date2';

that one won't use an index and with Y2k stuff just very well might be totally incorrect.

>Thanks
>
>Alexander T. Agung
>
>

--
See http://osi.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 Wed Feb 09 2000 - 07:08:34 CST