How to incrementally count the number of repeated instances

Question

I want to incrementally count the number of repeated instances in a table, so if I have a table like this:

id   | name  | status |
-----------------------
1    | John  |    1   | 
2    | Jane  |    1   |
4    | John  |    1   |
5    | John  |    1   |
6    | Jane  |    1   |
7    | John  |    1   |

Using the column "name" as reference, the output would be

id   | name  | status | count |
------------------------------
1    | John  |    1   |   1   |
2    | Jane  |    1   |   1   |
4    | John  |    1   |   2   |
5    | John  |    1   |   3   |
6    | Jane  |    1   |   2   |
7    | John  |    1   |   4   |

The DBMS implementation is MySQL, version 5.6

Answer 1

In MySQL 5.x, where window functions are not available, one option uses a correlated subquery:

select t.*,
    (
        select count(*)
        from mytable t1
        where t1.name = t.name and t1.id <= t.id
    ) rn
from mytable t

You could also do this with user variables:

select t.*,
    case when @name = (@name := name)
        then @rn := @rn + 1
        else @rn := 1
    end as rn
from (select * from mytable order by name, id) t
cross join (select @name := null, @rn := 0) x

There are pros and cons to both approaches. The second solution scales better than the first against a large dataset, however user variables are tricky in MySQL, and are now officially planned for deprecation in a future version.

Answer 2

Just use row_number():

select t.*, row_number() over (partition by name order by id) as count
from t
order by id;

Answer 3

SELECT t1.id, t1.name, t1.status, COUNT(*) `count`
FROM tablename t1
JOIN tablename t2 USING (name)
WHERE t1.id >= t2.id
GROUP BY t1.id, t1.name, t1.status
ORDER BY t1.id