Getting the number of rows with a GROUP BY query

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.

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

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.

Using sub queries :

SELECT COUNT(*) FROM    
(
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 
)    
as temp;

so temp contains the count of rows.

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

All ans given will execute the query and then find the count. Distinct is definitely slower than group by on large dataset.

Best way to find the count of group by is below

SELECT 
	sum(1) as counttotal
FROM (
	Your query with group by operator
) as T

This will find the count while calculating group by.