By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TEMPORARY TABLE temp_users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20),
color VARCHAR(20),
shape VARCHAR(20)
);
INSERT INTO temp_users(name, color, shape) VALUES
('john', 'blue', 'square'),
('mary', 'green', 'square'),
('anna', 'red', 'triangle'),
('bob', 'blue', 'square'),
('susan', 'blue', 'square'),
('frank', 'red', 'triangle');
Records: 6 Duplicates: 0 Warnings: 0
SELECT *,
COUNT(*) OVER (PARTITION BY color, shape) nb_duplicates,
DENSE_RANK() OVER (ORDER BY color, shape) duplicate_group_id
FROM temp_users
ORDER BY id;
| id | name | color | shape | nb_duplicates | duplicate_group_id |
|---|---|---|---|---|---|
| 1 | john | blue | square | 3 | 1 |
| 2 | mary | green | square | 1 | 2 |
| 3 | anna | red | triangle | 2 | 3 |
| 4 | bob | blue | square | 3 | 1 |
| 5 | susan | blue | square | 3 | 1 |
| 6 | frank | red | triangle | 2 | 3 |