Mysql count the number of rows returned
Often I want to count the number of rows returned by a mysql query. This is usually when I use group by combined with having.
For example, say I want to find the number of users who have first names that more than 20 other users also have.
At first I might try something like this:
1 2 3 4 5 |
SELECT COUNT(first_name) FROM users GROUP BY first_name HAVING COUNT(first_name) > 20; |
However, this returns a series of numbers. 2d6fxehgby This is not useful if I’m using ActiveRecord, since I can’t actually use that row count without doing extra stuff. What I really want is, rather, to get the number of rows.
The easiest way to do this is this:
1 2 3 4 5 6 |
SELECT COUNT(*) FROM (SELECT COUNT(first_name) FROM users GROUP BY first_name HAVING COUNT(first_name) > 20) AS temp; |
The above query simply returns the actual number of rows.
(via mike p in gchat)
September 22, 2009
