Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count rows in MySQL along with the actual row contents

Tags:

mysql

count

rows

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?

like image 380
Jesse Barnum Avatar asked May 22 '13 23:05

Jesse Barnum


People also ask

How do I count specific rows in MySQL?

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.

How do I count rows in MySQL by group?

In MySQL, the COUNT() function calculates the number of results from a table when executing a SELECT statement. It does not contain NULL values. The function returns a BIGINT value. It can count all the matched rows or only rows that match the specified conditions.

What is Row_count () in MySQL?

In MySQL the ROW_COUNT() function is used to return the number of rows affected by the previous SQL statement. If the previous statement was not one that could potentially change data rows or you can say, it wasn't an INSERT, UPDATE, DELETE or other such statement this function will return -1.

How can I count total rows in SQL?

SQL COUNT(), AVG() and SUM() FunctionsThe COUNT() function returns the number of rows that matches a specified criterion.


1 Answers

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

like image 144
Taryn Avatar answered Sep 20 '22 12:09

Taryn