Re: Counting Rows

"GZ" <gregz_at_yahoo.com> wrote in message news:3AB4B786.E7FFEFAD_at_yahoo.com...
> I would like to know how to get a count of the number of rows returned
> by a query WITHOUT having to re-run the query.
>
> The query looks something like this:
>
> select dept, num_persons
> from(
> select dept, num_persons from this_table where.....
>
> UNION ALL
>
> select dept, num_persons from that_table where.....
> )
>
> The end report would look like:
>
> Dept Persons
> ---- -------
> A 4
> B 5
> C 9
> TotalDepts 3
>
> I can achieve this result by putting the whole query again in the
> sub-query with another UNION ALL and wrapping it in a "select count
> from" to get the number of departments, but since we are talking about a
> million rows or more, the time needed for the whole query is doubled. Is
> there another way of doing this more easily?
>
> Greg
>

If in sqlplus (you provide very little background, and force some mindreading)
you could simply
break on report
compute count of dept on report
<your sqlstatement>
You won't get the prompt you want, but you will get results. In pl/sql you could simply use sql%rowcount

Regards,

Sybrand Bakker, Oracle DBA Received on Sun Mar 18 2001 - 10:13:58 CST