SQL Count each occurence of words separated by comma

I have a column in a table with words separated by comma. I need to count each occurence of each word
My column looks like : (‘a, b, c’), (‘a, b, d’), (‘b, c, d’), (‘a’), (‘a, c’); (fiddle at the bottom)

Here is what I get :

MyCol        Count
-----------------
a           1
a, b, c     3
a, b, d     3
a, c        2
b, c, d     3

But here is what I expect

MyCol    Count
-------------
a        4
b        3
c        3
d        2

Here is what I’ve done so far :

select MyCol, COUNT(*)
from Test
cross apply string_split(MyCol, ',')
group by MyCol

Fiddle : http://sqlfiddle.com/#!18/4e52e/3

Please note the words are separated by a comma AND a space

Answer

You are using the wrong column. Simply use the [value] column (returned from the STRING_SPLIT() call) and remove the space characters (using TRIM() for SQL Server 2017+ or LTRIM() and RTRIM() for earlier versions):

SELECT TRIM(s.[value]) AS [value], COUNT(*) AS [count]
FROM Test t
CROSS APPLY STRING_SPLIT(t.MyCol, ',') s
GROUP BY TRIM(s.[value])
ORDER BY TRIM(s.[value])