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.
To count the number of rows, use the id column which stores unique values (in our example we use COUNT(id) ). Next, use the GROUP BY clause to group records according to columns (the GROUP BY category above). After using GROUP BY to filter records with aggregate functions like COUNT, use the HAVING clause.
So, if you want to count quantity of groups, not quantity of elements in each group, and return duplicate value to every group record in result table, you should use OVER() clause on you'r count function.
To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.
The SQL GROUP BY Statement The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With