Show all the groupped value that have same value more than 1

Question

I have table like this

table1

| ID  |  Val |  Val2  |
|  2  |  AA  |   0    |
|  3  |  AA  |   1    |
|  4  |  AD  |   0    |
|  5  |  CV  |   1    |
|  6  |  AF  |   1    |
|  7  |  CV  |   1    |

I want to know if there is duplicate value in column Val more than one. I used the group by clause. plus i want to know how many duplicate appear in Val

select Val,count(Val) from table 
group by Val where 
having count(val) > 1

result :

| Val | count(val) |
| AA  |   2        | 
| CV  |   2        |

Now i want to know which column that have duplicate value so i used Group_concat with query like this

select Val,count(Val), group_concat(ID) from table1 
group by Val where 
having count(val) > 1

Results

| Val | count(val) | group_concat(ID) |
| AA  |   2        |  2,3             |
| CV  |   2        |  5,7             |

Now i dont know how to show all the duplicate value, i only show which id that had duplicate value by group_concat() but i couldn't show all data without the group_concat column. I tried to use Field_in_set but it seems not working.

select Val,count(Val), group_concat(ID) from table1 
where FIELD_IN_SET(ID,group_concat(ID))
group by Val where 
having count(val) > 1

What i expect is i want to show all the duplicate value after i group by and count the value like below table

| ID  |  Val |  Val2  |
|  2  |  AA  |   0    |
|  3  |  AA  |   1    |
|  5  |  CV  |   1    |
|  7  |  CV  |   1    |

Answer 1

CREATE TABLE table1 (
  `ID` INTEGER,
  `Val` VARCHAR(2),
  `Val2` INTEGER
);

INSERT INTO table1
  (`ID`, `Val`, `Val2`)
VALUES
  ('2', 'AA', '0'),
  ('3', 'AA', '1'),
  ('4', 'AD', '0'),
  ('5', 'CV', '1'),
  ('6', 'AF', '1'),
  ('7', 'CV', '1');

SELECT  t1.* 
FROM table1 t1 
  INNER JOIN (SELECT COUNT(`Val`) countval, `Val`  FROM table1 GROUP BY `Val`) t2 
    ON t1.`Val` = t2.`Val`
WHERE countval > 1

ID | Val | Val2
-: | :-- | ---:
 2 | AA  |    0
 3 | AA  |    1
 5 | CV  |    1
 7 | CV  |    1

SELECT `ID`, `Val`, `Val2`     
FROM ( SELECT
         `ID`, `Val`, `Val2`,
         COUNT(`Val`) OVER(PARTITION BY `Val`) c1
       FROM table1) t1
   WHERE c1 > 1

ID | Val | Val2
-: | :-- | ---:
 2 | AA  |    0
 3 | AA  |    1
 5 | CV  |    1
 7 | CV  |    1

db<>fiddle here