Extracting number of rows from a sql table

user_1255733 0
Viewed 249

Let's say we have a SQL request, such as select * from employees. If I want to know only the number of rows, then this can be done by select count(*) from employees.

The problem is that this require modifying the SQL request which may require some parsing. If the request is a string SQL_request, I would like to write something generic, simple and robust like: select count(*) from (SQL_request), but the resulting request (that is select count(*) from (select * from employees)) is not a correct one.

It should be noted that in some cases we can do such construction. E.g. if one want the top 1000 rows of a request, then one can do (SQL_request) limit 1000.

Could there be a way to encapsulate a generic SQL table request so that one could get the number of rows?

encapsulation sql
2 Answers

...but the resulting request that is select count(*) from (select * from employees)) is not a correct one...

You are using your query as a "table expression" but you forgot to assign an alias to it. Try:

select count(*) from (select * from employees)) x

See the x at the end?

user_64361910

It depends on the database. Sometimes, it is sufficient to do:

select count(*)
from (<request>) t;

The following issues comes to mind:

  1. If "request" ends with an order by, some databases will not permit it.
  2. If "request" has CTEs, some databases will not permit it.
  3. If "request" is assigning values of variables, some databases will not permit it.

Those come to mind. For many purposes, you can encapsulate using a subquery or CTE.

user_11440350
Related