Subject Re: [ib-support] count records
Author Helen Borrie
At 07:53 AM 23-08-02 -0700, you wrote:
>Hi all!
>Newbie question again:
>Need help with a statement that I´m sure you find very
>I need to count records in 2 tables that have a field
>that is '100%' and add the counted records together.
>The tables have no relation.
>It would work like this:
>select count(aktid) from akt
>where akt.avtaldebid = '100%' + select count(kliid)
>from kli where kli.avtaldebid = '100%'
>How would you suggest that I get the correct data.

Nearly right...

count(aktid) +
(select count(kliid) from kli where avtadelbid = '100%')
as allcount
from akt
where avtaldebid = '100%' ;


count(a.aktid) + count(k.kliid) as allcount
from akt a
join kli k
on a.avtaldebid = k.avtaldebid
where a.avtaldebid = '100%' ;

Both slow queries....indexes would help...

A stored procedure might serve you better.

create procedure countem (inputvalue string)
returns allcount
declare variable interimcount integer;
interimcount = 0;
select count(aktid) from akt
where avtaldebid = :inputvalue
into :interimcount;
allcount = :interimcount;
select count(kliid) from kli
where avtaldebid = :inputvalue
into :interimcount;
allcount = :allcount + interimcount;
end ^

Query to get the value:
select allcount from countem ('100%')