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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.