--- In firebird-support@yahoogroups.com, "skysword76"
<skysword76@y...> wrote:

>
> Actually I wanted something like
>
> SELECT tNo, COUNT(tField > 0) AS POSITIVE,COUNT(tField < 0) AS NEGATIVE,
> COUNT(tField = 0) AS ZEROES (...etc)
> FROM table
> GROUP BY tNo
>
> Regards
>
> Radu
>

I imagine a selectable Stored Procedure could do this pretty
efficiently, I can imagine you could use something like this but it
wont be too pretty.

select distinct T.TNo,
(select count(*) from Table T1 where T.TNo = T1.TNo and TField > 0)
as Positive,
(select count(*) from Table T2 where T.TNo = T2.TNo and TField = 0)
as Zeroes,
(select count(*) from Table T3 where T.TNo = T3.TNo and TField < 0)
as Negative
from Table T

A stored procedure could do it relatively easy, something like this.

set term ^;

create procedure sp_breakdowntno
returns
(
tno integer,
neg integer,
zer integer,
pos integer
)
as
declare variable oldtno integer;
begin
oldtno = -1; -- some impossible value;
for select tno, tfield
from table
order by tno
into :tno, :tfield do
begin
if (:oldtno <> :tno) then
begin
if (:oldtno <> -1) then suspend;
neg = 0;
zer = 0;
pos = 0;
end
else
begin
if (:tfield < 0) then neg = :neg + 1
else if (:tfield = 0) then zer = :zer + 1
else pos = :pos + 1;
end
end
if (:oldtno <> -1) then suspend;
end
^

set term ;
^

commit;

select *
from sp_breakdowntno;
etc.

Adam