Problem solve Get help with specific problems with your technologies, process and projects.

Result set row count along with query results

Can I get both the result of the query and the number of rows returned in a single query?

Every database has a facility to return a status code to indicate whether a query was successful. Along with this, the database also usually returns the number of rows processed. For example, if you run a SELECT query in Oracle, it will say "nn rows selected." Microsoft's SQL/Server will say "nn rows affected" (even though the rows aren't really "affected" by a SELECT).

If you are using an application with scripting capabilities, like a Web interface developed with ASP or PHP or ColdFusion, for example, then this row count is also available for every query you run.

<cfquery name="getrecs" datasource="#yourdb#">

  select foo, bar
    from yourtable
   where baz = "quux"

<cfoutput> <p> The number of records is: #getrecs.RecordCount# </p> </cfoutput>

You can also retrieve the number of records via SQL, although it's a bit of a hack, because in order to do it in one query, you have stretch the definition of "one query" to cover a UNION with two subqueries.

( select 1 as rowtype
       , foo
       , bar
    from yourtable  
   where baz = "quux" )
union all
( select 2 
       , count(*)
       , sum(bar)
    from yourtable
   where baz = "quux" )
order by rowtype, foo

A good optimizer should be able to detect the same WHERE clause and not make two complete passes of the table. Note that the rowtype column is used both to distinguish "detail" rows from the "total" row, and also as the major sort field, so that the total row comes last. The count of rows is returned in the foo column, and just for fun, the sum of the bar column values is returned in the bar column.

So this "hack" is actually a good hack, in that it can be used for other aggregates like SUM() for which no standard mechanism (like the number of rows processed) is provided.

For More Information

Dig Deeper on Oracle and SQL