Counting Rows

From: GZ <gregz_at_yahoo.com>
Date: Sun, 18 Mar 2001 13:06:50 GMT
Message-ID: <3AB4B786.E7FFEFAD@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 Received on Sun Mar 18 2001 - 07:06:50 CST