Frank wrote:

> Maybe I worked to long but I have no clue how to solve this !
>
> How can I count (in Firebird SQL) the number of rows returned by this
> query ?
> (I need to use this value in a stored procedure)
>
> SELECT
> AUTO_ID_CUSTOMER
> , SUM(UNIT_QTY*UNIT_PRICE)
> FROM V_TRANSACTIONS
> WHERE INV_DATE > '01/01/2006'
> GROUP BY AUTO_ID_CUSTOMER
> HAVING SUM(UNIT_QTY*UNIT_PRICE) < 100000

I think something like

select count(distinct v1.auto_id_customer)
FROM V_TRANSACTIONS v1
WHERE v1.INV_DATE > '01/01/2006'
and (select sum(v2.UNIT_QTY*v2.UNIT_PRICE)
FROM V_TRANSACTIONS v2
where v2.auto_id_customer = v1.auto_id_customer
and v2.INV_DATE > '01/01/2006') < 100000

may be what you are looking for. Alternatively, the RowsSelected
property of IBO returns the number of rows selected once you get to the
end of your result set (at least).

Though why is this number interesting to know? If it is because you want
to provide the management with some statistical analysis tool that tells
them what kind of customers they have, then that is fine. If it is to
tell the user how many rows are left of a result set, then it is
possibly not what you want (it is often a more expensive value to get
than the value of the information to the user).

Oh, and next time - ask such a question on the firebird-support list (a
forum for general firebird questions) rather than the ibobjects list (a
forum for specific questions about the ibobjects components).

HTH,
Set