SQL count results from group by

Post Reply
mister_v
Posts: 188
Joined: Thu Mar 04, 2010 9:19 pm

SQL count results from group by

Post by mister_v »

Hi,

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

Code: Select all

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.
chris
Site Admin
Posts: 194
Joined: Mon Jul 21, 2008 9:52 am

Re: SQL count results from group by

Post by chris »

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.
Post Reply