Webmaster2020.com

Posted: **Wed Aug 22, 2012 3:18 pm**

Hi,

I would like to know the total rows returned by a "Group by"-query.

SELECT table1.id, table2.name,table3.name FROM table1, table2, table3 
WHERE (associate table1, table2 and table3 to each other) 
GROUP BY table1.id

I could use count() but together with GROUP BY, it gives the subtotals for each group.
And that is not what i want.

Posted: **Wed Aug 22, 2012 3:52 pm**

There is a nice solution in MySQL.

Add the keyword SQL_CALC_FOUND_ROWS right after the keyword SELECT :

Code: Select all

SELECT SQL_CALC_FOUND_ROWS table1.id, table2.name,table3.name FROM table1, table2, table3 
WHERE (associate table1, table2 and table3 to each other) 
GROUP BY table1.id LIMIT 10,20

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

Code: Select all

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/ ... found-rows

If you don't use MySQL, you can use sub-queries it should always work

Code: Select all

SELECT COUNT(*) FROM
( table1.id, table2.name,table3.name FROM table1, table2, table3 WHERE (associate table1, table2 and table3 to each other) GROUP BY table1.id )
AS temp;

so temp contains the count of rows.

Hopes this helps.

Webmaster2020.com

SQL count results from group by

SQL count results from group by

Re: SQL count results from group by