By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE sip_count (
`sip_id` INTEGER,
`sip` INTEGER,
`sip_count` INTEGER
);
INSERT INTO sip_count
(`sip_id`, `sip`, `sip_count`)
VALUES
('1', '1', '2'),
('1', '1', '2'),
('2', '1', '3'),
('2', '1', '3'),
('2', '1', '3'),
('4', '1', '2'),
('4', '1', '2'),
('3', '1', '3'),
('3', '1', '3'),
('3', '1', '3');
SeLECT
`sip_id`, `sip`,
ROW_NUMBER() OVER(PARTITION BY `sip_id`) AS 'sip_count'
FROM sip_count
| sip_id | sip | sip_count |
|---|---|---|
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 2 | 1 | 1 |
| 2 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 1 | 1 |
| 3 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 1 |
| 4 | 1 | 2 |