It is a bit more confusing, but still doable

CountTable (X, Y, Amt);

Then use your insert and delete triggers to fill the table

X Y Amt
==============
1 1 1
1 2 1
2 3 1
1 1 1
1 2 1
1 2 -1

etc

Your query on this table would look something like

select sum(Amt)
from counttable
where Y in (1,2);

your CountTable SP will have either a group by or nested queries to
work out the sum for that X/Y combination, but in a nutshell,

1) Sum the values of interest in the counttable.
2) Delete the values of interest in the counttable.
3) Insert a single value calculated in (1)

The problem with count(*) is that it requires n complexity, so if you
have 4 million records, it needs to add 4 million 1's together. With
this methodology, providing the maintenance procedure is run
reasonably often, you will probably only be adding together 400 1's.

Adam




--- In firebird-support@yahoogroups.com, Dennis Mulder
<damulder@w...> wrote:

> And how do you get a fast count when having the following table
>
> TABLE (
> X INT NOT NULL,
> Y CHAR NOT NULL,
> PRIMARY KEY (X,Y)
> )
> and want to know the number of records that satisfies for Y=Y1 and

Y=Y2?

>
> eg.
>
> X Y
> ------------
> X1 Y1 <-----
> X2 Y1
> X1 Y2 <-----
> X2 Y3
> X3 Y1 <-----
> X3 Y2 <-----
>
> The result here would be count(*) =2
>
> The way I count is:
>
> SELECT COUNT(*)
> FROM TABLE T
> WHERE T.Y = Y1
> AND T.X IN (SELECT T.X FROM T WHERE T.Y = Y2)
>
> whereby having Y1 the value with the lowest count (maintained in a

table

> like you suggested).
> Is there also a faster way for this scenario?
>
> Dennis
>
>
> Nigel Weeks wrote:
>
> > I never saw that method when it first came around, but man it's

nice.

> > That's another solution for the 'Handy Bits' folder!
> >
> > N