CODEWITHSUNDEEP
mysql
raj_on_rails
raj_on_rails

Reputation: 144

How to get the count as well as all the rows of the table in mysql?

This is my query:

select location_id, location_name, display_name from location ;

and the result is:

+---------------+------------------------------------+----------------+
| location_id   | location_name                      | display_name   |
+---------------+------------------------------------+----------------+
|             1 | L1 PQR MULTIPLEX AND ENTERTAINMENT | L1 PQR         |
|             2 | Cinepolis                          | Cinepolis KP   |
|             3 | PVR FORUM MALL                     | PVR KP         |
|           333 | PRASHANTH CINEMAS                  | PRASHANTH MP   |
|          4555 | RAVI CINEMAS                       | RAVI KP        |
|        323213 | ASIAN GPR MULTIPLEX                | ASIAN KPHB     |
| 5000721013770 | PVR CENTRAL                        | PVR PUNJAGUTTA |
| 5000721017325 | PVR INORBIT                        | PVR HITECH     |
| 5000981019820 | TIVOLI CINEMAS                     | TIVOLI SC      |
| 5300181011396 | Central Panjaguttaddd              | ddd            |
+---------------+------------------------------------+----------------+
10 rows in set (0.00 sec)

I also need the count in this query with LIMIT 10 , i have tried as

select count(*) as count, location_id, location_name, display_name from location limit 10;

and the result is: 

+-------+-------------+------------------------------------+--------------+
| count | location_id | location_name                      | display_name |
+-------+-------------+------------------------------------+--------------+
|    50 |           1 | L1 PQR MULTIPLEX AND ENTERTAINMENT | L1 PQR       |
+-------+-------------+------------------------------------+--------------+
1 row in set (0.00 sec)

Why it is fetching only one record ??

How can i get the count as well as records ??

Upvotes: 0

Views: 89

Answers (2)

Saikios
Saikios

Reputation: 3711

You have 2 issues with what you wrote on there.

select count(*) as count, location_id, location_name, display_name from location limit 10;

The first one you shouldn't limit a count! Second a count is a "group by" instruction it will always give the same UNIQUE number,

What exactly are you trying to resolve?

If you want a 50 on each column (I don't understand why you would want something like that you could do

select (select count(*) from location) as count, location_id, location_name, display_name from location limit 10;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The count(*) in the select turns the query into an aggregation query. With no group by, this returns only one row. Actually, in most databases it would return an error because the SQL engine would not know what to do with the remaining columns. In MySQL they get indeterminate values.

If you want the overall count on each row, I would suggest that you use a join to get the result:

select lc.cnt as count, l.location_id, l.location_name, l.display_name
from location l cross join
     (select count(*) as cnt from location) lc
limit 10;

If, perchance, you actually want a sequential number on each row, then you are misunderstanding count(). For that, use a variable:

select (@rn := @rn + 1) as count, l.location_id, l.location_name, l.display_name
from location l cross join
     (select @rn := 0) vars
limit 10;

Upvotes: 3

Related Questions