By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table yourtable( id int, name varchar(10), score int);
insert into yourtable values(1, 'aaa', 10),
(2 ,'ccc', 20),
(3 ,'aaa', 10),
(4 ,'ddd', 15);
Records: 4 Duplicates: 0 Warnings: 0
SELECT value name, count(name) name_count
FROM
JSON_TABLE(
'["aaa", "bbb", "ccc"]',
"$[*]"
COLUMNS(
Value varchar(50) PATH "$"
)
) data left join yourtable on value=name
group by value;
| name | name_count |
|---|---|
| aaa | 2 |
| bbb | 0 |
| ccc | 1 |