Getting the number of rows with a GROUP BY query

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

Getting the number of rows with a GROUP BY query

Related

Recent Posts