Count rows in MySQL along with the actual row contents

Question

Is there a way in MySQL to do a single SQL statement that returns the selected rows along with the count of the result rows?

I can do this:

SELECT COUNT(*) FROM BigTable WHERE firstname LIKE 'a%';

Which gives me a single result row with the count (37,781). I can get the actual row data like this:

SELECT firstname FROM BigTable WHERE firstname LIKE 'a%';

which displays the actual 37,781 rows. But when I try to combine them, like this:

SELECT firstname, COUNT(*) FROM BigTable WHERE firstname LIKE 'a%';

I get a single row with the first row that matches the query, and the total count of records that matches the query.

What I'd like to see is two columns with 37,781 rows. The first column should contain the first name for each row and the second column should contain the number '37,781' for every row. Is there a way to write the query to accomplish this?

Houseroom

Answer 1

The cross join is not the efficient way, the better way is to use an inline SELECT like the following structure:

SELECT firstname, 
       (select count(*) from BigTable where firstname like 'a%') as count 
from BigTable  
where firstname like 'a%'

I tested both approaches with 50k records in database, and this approach is almost 2x faster.

Gilges

Yeah, doing it that way is fast - the speed is identical to not doing the COUNT(*) at all. Nice :-) – Houseroom

Answer 2

You can use a CROSS JOIN. The subquery will get the count for all firstnames and then it will include this value in each row:

SELECT firstname, d.total
FROM BigTable
CROSS JOIN 
(
   SELECT COUNT(*) total
   FROM BigTable
   WHERE firstname LIKE 'a%'
) d
WHERE firstname LIKE 'a%';

See SQL Fiddle with Demo

Transit

Thanks, that worked perfectly. Barmar's answer worked too, but this is slightly faster (about 5.75 seconds) than the other approach (about 6.5 seconds). – Houseroom

SQL Fiddle is awesome. I haven't heard of that. Thanks for the tip. – Coronel

@BrianMoore SQL Fiddle was written by a StackOverflow user! :) – Transit

Answer 3

You can join with a subquery:

SELECT firstname, ct
FROM BigTable
JOIN (SELECT COUNT(*) ct
      FROM BigTable
      WHERE firstname LIKE 'a%') x ON (1 = 1)
WHERE firstname LIKE 'a%'

Serviceberry

Thank you Barmar! I tested this and it did exactly what I want. – Houseroom

Answer 4

I feel like this used to be the case for older versions of MySQL, but this isn't working in my tests.

But according to the manual, http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_count Given that COUNT(*) is a group by function, and naturally groups all of the rows together, when a GROUP BY statement is not attached, I can only see the solution to this being either multiple statements, or a sub-query. I would suggest running the 2 queries separately, if you can, but if that isn't possible, Try:

SELECT firstname,
    total
  FROM BigTable,
  ( SELECT COUNT(*) AS total
      FROM BigTable ) AS dummy
  WHERE firstname LIKE 'a%';

Coronel

This is close to what I'm looking for, although in the second column, I get the total number of records in the table, instead of the total that match the search. – Houseroom

I added the " WHERE firstname LIKE 'a%'" to the sub-query and now it's returning the correct response. Execution time is very similar to bluefeet's answer, about 6.1 seconds when run repeatedly. Thank you! – Houseroom