MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Create Table tableA(username VARCHAR(50), counts INT, cat VARCHAR(50));

Insert Into tableA Values('Jo',1,'A'),
('Jo',5,'B'),
('Jo',1,'C'),
('An',2,'A'),
('An',3,'B'),
('An',2,'C');

Records: 6  Duplicates: 0  Warnings: 0


select username,
       sum(counts) counts
from tableA
group by username, case when cat ='A' then 'B' else cat end
order by username


username
counts


An
5

An
2

Jo
6

Jo
1


select username,
       case when cat ='A' then 'B' else cat end cat,
       sum(counts) counts
from tableA
group by username, case when cat ='A' then 'B' else cat end
order by username


username
cat
counts


An
B
5

An
C
2

Jo
B
6

Jo
C
1


select username,
       case when cat in ('A', 'B') then 'AB' else cat end cat,
       sum(counts) counts
from tableA
group by username, case when cat in ('A', 'B') then 'AB' else cat end
order by username


username
cat
counts


An
AB
5

An
C
2

Jo
AB
6

Jo
C
1