SELECT COUNT all rows and return just 10 + and a counted total

Question

What I am trying to get is to send a request to DB that will:

1 count all rows
2 return 10 rows

SELECT count( * ) AS 'total'
FROM stuff
WHERE usr = '65'
LIMIT 10

So it is supposed to return 10 results PLUS 'total' with the number of all rows. So far it returns the counted amount of rows only....

not possible. you cannot mix aggregate functions with "normal" data like that. easier to run two separate queries. — Marc B, Feb 05 '14 at 04:09
@KyleMassacre COUNT isn'T affected by the LIMIT clause, it always counts all rows. — Johannes H., Feb 05 '14 at 04:11
@KyleMassacre - I need ALL rows in table to be counted but ONLY 10 to be returned... anyway - looks like it's not posisle in one go... =( — Rossitten, Feb 05 '14 at 04:13
To expand on what MarcB said, the basic problem is that your `stuff` table has multiple columns, and those columns are incompatible with just a single integer like `COUNT` returns. A `UNION` might have been feasible if the columns were compatible types in both queries. — jpmc26, Feb 05 '14 at 04:13
http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_found-rows — CBroe, Feb 05 '14 at 04:14

score 2 · Answer 1 · edited May 23 '17 at 10:25

2

You'll need to split the 2 concerns out, and then recombine them:

SELECT s.col1, s.col2, s.col3, x.total
FROM `stuff` s
CROSS JOIN 
(
   SELECT count(*) AS total
   FROM `stuff`
   WHERE `usr` = '65'
) x
WHERE s.`usr` = '65'
LIMIT 10;

Fiddle here RDBMs like SqlServer and Oracle allow for CTE's which would allow you to DRY up the repeated select ... where. Some options in MySql here

edited May 23 '17 at 10:25

Community

1
1

answered Feb 05 '14 at 04:12

StuartLC

104,537
17
209
285

+1. But, this seems to be duplicate, please find the similar answer here http://stackoverflow.com/questions/16703453/count-rows-in-mysql-along-with-the-actual-row-contents – Rajesh Omanakuttan Feb 05 '14 at 04:17
Hmm. I agree as it serves the purpose. – Rajesh Omanakuttan Feb 05 '14 at 04:21

score 2 · Accepted Answer · answered Feb 05 '14 at 04:13

-- count records first
SET @total = (
    SELECT count( * ) AS 'total'
    FROM `stuff`
    WHERE `usr` = '65'
);

-- then, select your ten records and include the total from previous operation
SELECT *, @total
FROM `stuff`
WHERE `usr` = '65'
LIMIT 10

score 0 · Answer 3 · answered Feb 05 '14 at 04:24

0

If you want all rows to be counted and limit the result to 10 then you can do it the following way

SELECT SQL_CALC_FOUND_ROWS * FROM `stuff` WHERE `usr` = '65' LIMIT 10 
SELECT FOUND_ROWS();

answered Feb 05 '14 at 04:24

Ibrahim Azhar Armar

25,288
35
131
207

SELECT COUNT all rows and return just 10 + and a counted total

3 Answers3