Getting the number of rows with a GROUP BY query

Question

I have a query to the effect of

SELECT t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 
WHERE (associate t1,t2, and t3 with each other) 
GROUP BY t3.id 
LIMIT 10,20

I want to know to many total rows this query would return without the LIMIT (so I can show pagination information).

Normally, I would use this query:

SELECT COUNT(t3.id) FROM t1, t2, t3 
WHERE (associate t1,t2, and t3 with each other) 
GROUP BY t3.id

However the GROUP BY changes the meaning of the COUNT, and instead I get a set of rows representing the number of unique t3.id values in each group.

Is there a way to get a count for the total number of rows when I use a GROUP BY? I'd like to avoid having to execute the entire query and just counting the number of rows, since I only need a subset of the rows because the values are paginated. I'm using MySQL 5, but I think this pretty generic.

Answer 1

You're using MySQL, so you can use their function to do exactly this.

SELECT SQL_CALC_FOUND_ROWS t3.id, a,bunch,of,other,stuff 
FROM t1, t2, t3 
WHERE (associate t1,t2, and t3 with each other) 
GROUP BY t3.id 
LIMIT 10,20;

SELECT FOUND_ROWS(); -- for most recent query

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Answer 2

There is a nice solution in MySQL.

Add the keyword SQL_CALC_FOUND_ROWS right after the keyword SELECT :

SELECT SQL_CALC_FOUND_ROWS t3.id, a,bunch,of,other,stuff FROM t1, t2, t3 
WHERE (associate t1,t2, and t3 with each other) 
GROUP BY t3.id 
LIMIT 10,20

After that, run another query with the function FOUND_ROWS() :

SELECT FOUND_ROWS();

It should return the number of rows without the LIMIT clause.

Checkout this page for more information : http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

Answer 3

Are the "bunch of other stuff" all aggregates? I'm assuming so since your GROUP BY only has t3.id. If that's the case then this should work:

SELECT
     COUNT(DISTINCT t3.id)
FROM...

The other option of course is:

SELECT
     COUNT(*)
FROM
     (
     <Your query here>
     ) AS SQ

I don't use MySQL, so I don't know if these queries will work there or not.