I have a query like this:
select * from table where id <= 10 limit 5; // table has +10 rows
The number of result in the above query ^ is 10 rows. Now I want to know, how can I get the number of total result in this query:
select * from table where col = 'anything' limit 5;
How to calculate the number of all results (regardless of limit
) in this ^ ?
Actually I want this number:
select count(*) as total_number from table where col = 'anything'
Now I want to know how can I get the number of total results without another query.
The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value. TIP: SELECT LIMIT is not supported in all SQL databases. For databases such as SQL Server or MSAccess, use the SELECT TOP statement to limit your results.
The LIMIT clause is used to specify the number of records to return. The LIMIT clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.
SQL SELECT TOP, LIMIT and ROWNUM KeywordsThe LIMIT , SELECT TOP or ROWNUM command is used to specify the number of records to return. Note: SQL Server uses SELECT TOP . MySQL uses LIMIT , and Oracle uses ROWNUM .
The LIMIT clause is used in the SELECT statement to constrain the number of rows to return. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integers.
Add a column, total
, for example:
select t.*
, (select count(*) from tbl where col = t.col) as total
from tbl t
where t.col = 'anything'
limit 5
As stated by @Tim Biegeleisen: limit
keyword is applied after everything else, so the count(*)
still returns the right answer.
You need the SQL_CALC_FOUND_ROWS option in your query and FOUND_ROWS() function to do this:
DECLARE @rows int
SELECT SQL_CALC_FOUND_ROWS * from table where col = 'anything' limit 5;
SET @rows = FOUND_ROWS(); --for a later use
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With