CODEWITHSUNDEEP
mysqlsql
Kadel
Kadel

Reputation: 83

Count multiple CASE occurrences in SQL

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

Upvotes: 7

Views: 3403

Answers (4)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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  
;

Upvotes: 3

nimour pristou
nimour pristou

Reputation: 153

try this: 

 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

Upvotes: 2

Shadow
Shadow

Reputation: 34231

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
...

Upvotes: 4

sagi
sagi

Reputation: 40481

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

Upvotes: 4

Related Questions