Read a few CASE cases in SQL

Question

I am looking for a solution for the following problem:

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:

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

The result of this query:

  value count foos 3 bars 2 bakars 1 

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

 value count foos 3 bars 4 bakars 2 

Answer 1

There is no case in one expression, you cannot achieve what you want, because case stops at the first match.

To achieve the expected result, you need to have separate case or if() expressions for calling functions. If you don't mind having results in different columns, use conditional counting:

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

If you insist on getting counters in the same column, 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 occurrences in each line:

 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%' 

Answer 3

 select w.word ,count(*) from table_a join ( select 'foo' as word union all select 'bar' union all select 'bakar' ) w on value like concat('%',w.word,'%') group by w.word ; 

Answer 4

try the following:

  SELECT (select count(p1.value) from table_a p1 where p1.value LIKE '%foo%') as foos, (select count(p2.value) from table_a p2 where p2.value LIKE '%bar%') as bars, (select count(p3.value) from table_a p3 where p3.value LIKE '%bakar%') as bakars FROM table_a p GROUP BY p.value