Multivalue row count in MySQL Query

Question

Input:

col1
a,b
a,c

Output:

col1   count
a      2
b      1
c      1

I want to simply count multivalue rows in MySQL Query.

You would need to tell us which _version_ of SQL you are using (e.g. MySQL, SQL Server, Oracle, Postgres). But in any case, note that storing CSV in your SQL tables is generally bad practice. — Tim Biegeleisen, Aug 30 '19 at 10:39

score 0 · Answer 1 · answered Aug 30 '19 at 12:19

If your "table" is a large table or view, then you can use cross join to generate the rows with only one table reference:

select (case when n.n = 1 then col1 else col2 end) as col,
       count(*)
from t cross join
     (select 1 as n union all select 2) n
group by col;

1 Answers1