Count multiple CASE occurrences in SQL

Question

I'm looking for solution for the following issue:

SELECT CASE WHEN p.value LIKE '%foo%' THEN 'foos'
    WHEN p.value LIKE '%bar%' THEN 'bars'
    WHEN p.value LIKE '%bakar%' THEN 'bakars'
    ELSE p.value END as value,
COUNT(*) as count FROM table_a p GROUP BY value

Values are something like:

foo, bar, foo and bar, bakar, bakarbar, foobar

Result of this query is:

 value    count
 foos       3
 bars       2
 bakars     1

This code successfully counts occurrences, but the CASE stops at first match. Is there a way to do this?

value    count
 foos       3
 bars       4
 bakars     2

Answer 1

In a single case expression no, you cannot achieve what you want precisely because case stops at the first match.

You need to have separate case expressions or if() function calls to achieve the expected outcome. If you do not mind having the results in different columns, then use conditional counting:

select count(if(p.value LIKE '%foo%',1,null)) as foos,
       ...
from table_a p

If you insist on receiving the counts in the same column, then use union:

select 'foos' as `value`, count(*) from table_a where table_a.value LIKE '%foo%'
union
...

Answer 2

You can use UNION if you want to count more than 1 occurrence in each string:

SELECT 'foos' as value, count(*)
FROM YourTable
WHERE p.value LIKE '%foo%'
UNION ALL
SELECT 'bars' as value, count(*)
FROM YourTable
WHERE p.value LIKE '%bar%'
UNION ALL
SELECT 'bakars' as value, count(*)
FROM YourTable
WHERE p.value LIKE '%bakar%'