Given a list, how to get the count from mysql table?

Given a string list, I want to get the count of each item from the table. However, I don’t know how to get the count(0) of item which does not exist in the table.

for example, I have a table as follow.

id   name      score
------------------------
1    aaa       10
2    ccc       20
3    aaa       10
4    ddd       15

Given a list [“aaa”, “bbb”, “ccc”] , I hope a query can return me a result like

aaa 2
bbb 0
ccc 1

I tried “select name, count(*) from table where name in (“aaa”, “bbb”, “ccc”) group by name;”, but the result is without the count of “bbb”. Can I do it in a single query?

Answer

Your values list must be a rowsource, not a criteria in WHERE.

SELECT criteria.name, COUNT(table.score)
FROM ( SELECT 'aaa' name UNION ALL
       SELECT 'bbb'      UNION ALL
       SELECT 'ccc' ) criteria
LEFT JOIN table USING (name)
GROUP BY name