AVG and SUM in subquery return wrong values

[mdaet...@gmail.com]

Hi
I have the following query aggregating values over a column and
creating the sum over another column. Here everything works out fine.

SELECT c.timestamp, AVG(c.response) as response, SUM(c.quantity) as
quantity, c.trans
FROM
tbl_repo01_dest c
GROUP BY c.trans, c.timestamp

No I embed this query into another, more complex one to join the
results of the query with another table.

SELECT
b.timestamp,
b.response as response_cics,
a.response as response_arch,
b.quantity as quantity_cics,
a.quantity as quantity_arch,
b.trans as trans_cics,
a.trans as trans_arch
FROM
(tbl_repo06_dest) b ,
(SELECT c.timestamp, AVG(c.response) as response, SUM(c.quantity)
as quantity, c.trans from tbl_repo01_dest c group by c.trans,
c.timestamp) a
WHERE
a.trans=b.trans
AND
to_date(a.timestamp,'DD.MM.YYYY HH24:MI')=to_date
(b.timestamp,'DD.MM.YYYY HH24:MI')

The join works out fine and all the matching tuples are joined.
However, the columns constructed with the AVG and SUM Functions all
contain a value of 1 wich is not correct. I tried to use TO_NUMBER
both in the inner and outer SELECT without a result.

Any help is greatly appreciated
Bye
Marc

[GreyBeard]

On Tue, 15 Mar 2005 10:40:15 -0800, marc blathered into the void,
expecting a response:

http://groups.google.ca/groups?hl=en&lr=&group=comp.databases.oracle&selm=pan.2005.03.09.14.15.32.110766%40gmail.com

[Ed Prochak]

Are you sure the first query works????

Noticing that you group by a timestamp, I would not be surprised if
there was only one row with a given timestamp value. I do not think
Data types conversion is your problem.

Try to break the problem down into managable, TESTABLE pieces. (Hint:
go back to that first query and run it. I expect you'll be surprised at
the results.)

HTH,
Ed