At 01:12 AM 11/11/2003 +0000, you wrote:

>Hi,
>
>I just figured it out... the last table must be RDB$DATABASE and not
>ABNTRADEDATA.
>It works fine now.
>
>However, is there an easier way to do something like:
>SELECT (SELECT avg(volume), avg(price) from ABNTRADEDATA WHERE
>volume>0), (SELECT.....
>
>I guess the only way to achieve something like this is to do an
>individual select for all of them like:
>SELECT (SELECT avg(volume) FROM ABNTRADEDATA WHERE VOLUME >0),
>(SELECT(avg(price) FROM ABNTRADEDATA WHERE VOLUME >0), (SELECT
>avg(volume) from ABNTRADEDATA WHERE VOLUME <0);
>
>or is there a better way?

A stored procedure.

create procedure GetAverages
returns (
AvPriceForPos numeric(18,2),
AvVolPos numeric (18,2),
AvVolNeg numeric (18,2)
)
as
begin
select
avg(volume), avg(price) from abntradedata
where volume > 0
into :AvVolPos, :AvPriceForPos;
select avg(volume) from abntradedata
where volume < 0
into :AvVolNeg
end

That's two queries but no subqueries to slow you down.

select * from GetAverages;

Assuming you want to restrict this in some way, add some input parameters
for start and end dates, or whatever.

h.