Problem with aggregates functions in subquerys

"Abaco Informática S.A." <[email protected]> Wed, 27 Aug 2003 10:00:27 -0300
Newsgroups gmane.comp.db.sapdb.general
Message-ID <004201c36c9b$4242a360$e02a46c8@internet2>
Problem with returning value of aggregate functions in subquerys

Look at this example:

CREATE TABLE T1 (CLAVE FIXED(3), COD_1 FLOAT, VAL_1 FIXED(15,4))
//
INSERT INTO T1 VALUES (1,0.4,0.4)
//
INSERT INTO T1 VALUES (2,0.4,0.4)
//
INSERT INTO T1 VALUES (3,0.4,0.4)
//
SELECT SUM(COD_1) as suma1, SUM(VAL_1) as suma2 FROM T1

the result is (1.2, 1.2) and that is right.
but in the query:

SELECT (SELECT SUM(COD_1) FROM T1) as suma1, 
                (SELECT SUM(VAL_1) FROM T1) as suma2 FROM DUAL

the result is (1, 1) and that is wrong

on the other hand, in the query:

SELECT (SELECT SUM(COD_1) + .9 FROM T1) as suma1, 
              (SELECT SUM(VAL_1) FROM T1)  as suma2 FROM DUAL

the result is 2, 1 then the problem is in returning value of subquery not in calculation itself, I think it's good!. 

The functions MAX, MIN work fine but AVG (for example) has the same problem.

Please if you have any suggestion to go around this problem until the bug is fixed, please tell us.

Thank you in advance.

Ing. Alejandra Ferraggine