How to Count Items in Comma Separated List MySQL

Mysql - count values from comma-separated field

Clever solution here on SO: How to count items in comma separated list MySQL

LENGTH(textfield) - LENGTH(REPLACE(textfield, ',', '')) + 1

EDIT

Yes you can select it as an additional column: and correcting with the CHAR_LENGTH from @HamletHakobyan's answer:

SELECT 
  ID, 
  textfield, 
  (CHAR_LENGTH(textfield) - CHAR_LENGTH(REPLACE(textfield, ',', '')) + 1) as total 
FROM table

How to count items in comma separated list MySQL

There is no built-in function that counts occurences of substring in a string, but you can calculate the difference between the original string, and the same string without commas:

LENGTH(fooCommaDelimColumn) - LENGTH(REPLACE(fooCommaDelimColumn, ',', ''))

It was edited multiple times over the course of almost 8 years now (wow!), so for sake of clarity: the query above does not need a + 1, because OPs data has an extra trailing comma.

While indeed, in general case for the string that looks like this: foo,bar,baz the correct expression would be

LENGTH(col) - LENGTH(REPLACE(col, ',', '')) + 1

Get Count of different values in comma separated row in mysql

SELECT 
  SUBSTRING_INDEX(SUBSTRING_INDEX(all_city, ',', num), ',', -1) AS one_city,
  COUNT(*) AS cnt
FROM (
  SELECT
    GROUP_CONCAT(city separator ',') AS all_city,
    LENGTH(GROUP_CONCAT(citySEPARATOR ',')) - LENGTH(REPLACE(GROUP_CONCAT(citySEPARATOR ','), ',', '')) + 1 AS count_city
  FROM table_name
) t
JOIN numbers n
ON n.num <= t.count_city
GROUP BY one_city
ORDER BY cnt DESC;

for getting count of comma separated distinct value run above query but getting correct resulr you should use one more table **numbers** which have only one column num integer type and insert some values.
if you getting error during GROUP_CONCAT(city separator ',') AS all_city in this condition set a global variable " SET group_concat_max_len = 18446744073709547520; "

MySQL: count comma separated values from field

Join the tables and aggregate:

select t.tag, count(p.id) counter
from tags t inner join posts p
on find_in_set(t.tag, p.tags)
group by t.tag

The function find_in_set() used in the ON clause will work if there are no spaces after the commas in the column tags of the table posts. If there are spaces then replace() must be used to remove them.

See the demo.

Results:

| tag     | counter |
| ------- | ------- |
| City    | 3       |
| College | 1       |
| School  | 1       |
| Work    | 1       |

How to count how many comma separated values in a GROUP_CONCAT

Use COUNT to count them.

SELECT Group_concat(DISTINCT( p.products_id )) AS comma_separated,
       COUNT(DISTINCT p.products_id) AS product_count
FROM ...

SQL: Count of items in comma-separated column in a table

You can use CROSS_APPLY with STRING_SPLIT to create rows from the comma separated data values, and then COUNT the occurrences of each value:

SELECT value as [Holiday], COUNT(*) AS [Count]
FROM OhLog
CROSS APPLY STRING_SPLIT([Holidays], ',')
GROUP BY value

Output:

Holiday     Count
1           2
2           3
3           2
4           1
5           1

Demo on dbfiddle

If your database compatibility version is not at least 130, you won't have access to STRING_SPLIT. You can modify the compatibility version as described in the manual, or alternatively, use this query (based on this answer):

SELECT [Holiday], COUNT(*) AS [Count]
FROM (SELECT Split.a.value('.', 'NVARCHAR(MAX)') [Holiday]
      FROM (SELECT CAST(''+REPLACE([Holidays], ',', '')+'' AS XML) AS String
            FROM Ohlog
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)) AS O
GROUP BY [Holiday]

Output is the same as for the prior query. Demo on dbfiddle

Mysql count cells containing comma separated values

if you want to count multi rows (rows with value that contains 2 or more numbers) just look for any comma in the value

SUM(CASE WHEN value LIKE '%,%' THEN 1 ELSE null END) as multi

---

Related Topics