Display duplicate column with increasing count in separate coloumn in sql

I want to increase count in count column one by one if the same Id is found in records.

Don’t consider about auto increment of Id.

MySQL version 8.0 and you can order by name column.

I tried but not getting any solution.

My table Employee looks like this

       +------+-------+-------+
       | Id   | Name  |address|
       +------+-------+-------+
       | 111  | Sam   |  xyz  |
       | 112  | Mike  |  xyz  |
       | 113  | Carol |  xyz  |
       | 113  | Bob   |  xyz  |
       | 113  | John  |  xyz  |
       | 116  | Adam  |  xyz  |
       | 116  | David |  xyz  |
       +------+-------+-------+
    

I want to run sql query which will give output like this, if Id is same increase count

    +------+-------+-------+
    | Id     | Name  | count |
    +------+-------+-------+
    | 111    | Sam   |  1    |
    | 112    | Mike  |  1    |
    | 113    | Carol |  1    |
    | 113    | Bob   |  2    |
    | 113    | John  |  3    |
    | 114    | Adam  |  1    |
    | 114    | David |  2    |
    +------+-------+-------+

Answer

The ROW_NUMBER() function can generate your current count column, more or less:

SELECT
    Id,
    Name,
    ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Name) count
FROM yourTable
ORDER BY
    Id,
    Name;

Note that ROW_NUMBER requires MySQL 8+.