getting the total count for query with group by
I ran into the need of getting the total flattened count for pagination purposes when using a group by clause in a query.
A stripped down example of the structure of this query would look like the following.
SELECT COUNT(*)
FROM table as t1
INNER JOIN table2 AS t2 ON t1.id = t2.t1_id
GROUP BY t1.id
As one would surmise, the results would be count of the records for grouping.
| count | -------- | 1 | | 2 | | 20 | | 3 | | 17 |
The conclusion was to do another select count wrapping the first query. After some trial and error and intensive googling, searching the mysql docs, and mysql books, I was still getting no where.
I found the answer only by searching stackoverflow.com. To which should be my default geek search engine. (There really is a market for niche search engines).
The answer looks like this, the first query being wrapped with a count selecting from the query.
SELECT COUNT(*)
FROM (SELECT COUNT(*)
FROM table as t1
INNER JOIN table2 AS t2 ON t1.id = t2.t1_id
GROUP BY t1.id)
tmp
Thus for ending my torment and suffering, I bumped up Marcus Adams’s answer on stackoverflow and must give mad props in this journal entry.