CODEWITHSUNDEEP
mysqlsql
suihe dolood
suihe dolood

Reputation: 107

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?

Upvotes: 0

Views: 162

Answers (2)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15893

Though you got your appropriate answer I am sharing here another way of doing it using JSON_TABLE()

 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);

Query:

 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;

Output:

name name_count
aaa 2
bbb 0
ccc 1

db<fiddle here

Upvotes: 0

Akina
Akina

Reputation: 42632

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

Upvotes: 1

Related Questions