AVG and SUM in subquery return wrong values

From: marc.daetwyler_at_bawag.com <mdaetwyler_at_gmail.com>
Date: 15 Mar 2005 10:40:15 -0800
Message-ID: <b118382f.0503151040.31a9ea36@posting.google.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 Received on Tue Mar 15 2005 - 12:40:15 CST