select count() in a view

Question

Well, if I try to count the total of rows of a view like

select count(*) from my_view v

It always returns 1 knowing that the view has a lot of rows. I do not find any documentation about it in mysql docs. If it is not possible, is there a workaround to achieve this task? thank you.

Edit, the view has not primary key, then I also tried something like this with the same result of 1:

select count(v.id_customer) from my_view v

Here is an example:

mysql> select * from plans limit 5;
+----------------+----------+---------+---------+------------+---------------------+---------------------+----------------+--------------+
| medical_center | customer | invoice | product | recurrence | placed              | due_date            | elapsed_months | elapsed_days |
+----------------+----------+---------+---------+------------+---------------------+---------------------+----------------+--------------+
|              1 |        1 |       1 |       2 |          6 | 2015-01-18 17:16:23 | 2015-07-18 17:16:23 |              2 |           89 |
|              1 |        1 |       3 |       2 |          6 | 2015-04-18 17:16:23 | 2015-10-18 17:16:23 |              5 |            0 |
|              1 |        1 |       4 |       2 |          6 | 2015-04-18 17:16:23 | 2015-10-18 17:16:23 |              5 |            0 |
|              1 |        1 |       5 |       2 |          6 | 2015-04-18 17:16:23 | 2015-10-18 17:16:23 |              5 |            0 |
|              2 |        1 |       6 |       2 |          6 | 2015-04-18 17:16:23 | 2015-10-18 17:16:23 |              5 |            0 |
+----------------+----------+---------+---------+------------+---------------------+---------------------+----------------+--------------+
5 rows in set (0.00 sec)

Now, trying to counting:

mysql> select count(*) from plans p;
+----------+
| count(*) |
+----------+
|       1 |
+----------+

Answer 1

If your view calculates the count, eg

create view my_view as
select count(*) as count from my_table

then just:

select * from my_view

The view returns only one row (the count rssult), so if you select the count of the view rows, of course it will be 1.

Answer 2

Try this:

SELECT * , (select count(*) FROM my_view) AS Cnt FROM my_view

count(*) is an aggregate function that would need a group by clause to work. But the above is a workaround to help you now.