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

Getting a COUNT(*) of the rows produced by a query

I'm trying to do a count(*) on a select statement just to return the total number of rows from the select statement, and I can't seem to get it right.

I'm trying to do a count(*) on a select statement just to return the total number of rows from the select statement,...

and I can't seem to get it right. Here is my select statement:

select distinct fleet.fleetid, fleet.fleetname, 
  fleetuserassoc.assocstatusid 
from fleet, fleetuserassoc 
where fleet.fleetid = fleetuserassoc.fleetid;

I simply need to return one value which is the number of rows this select statement returns.

Just wrap another query around your query, "on the fly" as it were, like this:

select count(*)
  from (
       your query goes here
       ) as d

So in your case you would write:

select count(*)
  from (
       select distinct 
              fleet.fleetid
            , fleet.fleetname
            , fleetuserassoc.assocstatusid 
         from fleet
       inner
         join fleetuserassoc 
           on fleetuserassoc.fleetid
            = fleet.fleetid
       ) as d

See also Finding the highest COUNT in a GROUP BY query (26 October 2006) for another example of this structure.

P.S. Sorry, yes, I rewrote your query using JOIN syntax. Couldn't help myself. Table list syntax is deprecated and dangerous.

This was last published in November 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close