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"
</cfquery>


<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

• Dozens more answers to tough SQL questions from Rudy Limeback.
• The Best SQL Web Links: tips, tutorials, scripts, and more.
• Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
• Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
• Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.