CODEWITHSUNDEEP
mysqlselectcount
John
John

Reputation: 1085

MySQL select specific amount and count all possible

I think this is simple question but google only gives me select and count results.

I'm wondering if it is possible to select from a table with a limit and count all the rows that would normally be affected by the query.

For example:

I got 100 rows in table cars and I select the first 20 I want the count how many cars there are in the cars table in one query.

Thanks in advance

Upvotes: 1

Views: 1399

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270617

If I understand your need (I understand it to be that you want the result of your limited query plus the number of rows in the full table), this can be done with a SELECT subquery, or with a JOIN against a subquery. Since you only expect one row back from the count query, you can use a cartesian join (with no ON clause). The count of all rows then appears as the same value for each column.

SELECT
  cars.*,
  carcount.numcars
FROM
  cars
  JOIN (SELECT COUNT(*) AS numcars FROM cars) carcount
LIMIT 20

Would produce a result like

make     model     numcars
ford     focus     100
ford     model t   100
...
...
20 rows, numcars always = 100

Upvotes: 1

eggyal
eggyal

Reputation: 125865

Use SQL_CALC_FOUND_ROWS and then use the FOUND_ROWS() function:

SELECT SQL_CALC_FOUND_ROWS * FROM ... LIMIT 100;
SELECT FOUND_ROWS();

Upvotes: 1

Related Questions