is there a way to select couple columns using a limit but in mean time get the total count of rows in that table

SELECT col1,col2 FROM table where col3=0 limit 500

just like above. what to add to get the total of rows in the table

thank you

CodePudding user response：

Try this, you only need to use one column in the count

SELECT COUNT(col3) FROM table where col3=0

And here's a reference for more information MySQL COUNT

Note, to keep it simple, you have to run two queries, this one for the count and yours for the records

CodePudding user response：

Your question lacks a precise problem statement. My understanding of your question is as follows:

All of the following is necessary:

1. Need to get every row from the table, such that col3=0.
2. Need to get the total number of rows in the table (whether they satisfy col3=0 or not).
3. Need to limit your result set to columns col1, col2, and have at most 500 rows.
4. Need to execute a single query, rather than two separate queries.

If this is correct interpretation of your question, then I would propose the following solution:

SELECT col1,col2, (SELECT COUNT(*) FROM table) AS total_rows FROM table where col3=0 limit 500

Such query would produce result where at most 500 rows from your table satisfy the condition col3=0 are present alongside total_rows, which tells the number of all the rows in the table.

Example CSV of the result:

clo1,col2,total_rows
a,b,1000
c,d,1000
d,e,1000

CodePudding user response：

according to found_rows()

The SQL_CALC_FOUND_ROWS query modifier and accompanying FOUND_ROWS() function are deprecated as of MySQL 8.0.17; expect them to be removed in a future version of MySQL. As a replacement, considering executing your query with LIMIT, and then a second query with COUNT(*) and without LIMIT to determine whether there are additional rows

instead of these queries:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT FOUND_ROWS();

Use these queries instead:

SELECT * FROM tbl_name WHERE id > 100 LIMIT 10;
SELECT COUNT(*) FROM tbl_name WHERE id > 100;

COUNT(*) is subject to certain optimizations. SQL_CALC_FOUND_ROWS causes some optimizations to be disabled.

you can check these questions

how-to-get-number-of-rows

mysql-what-is-the-row-count-information-function-for-select