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

This may help http://stackoverflow.com/questions/5045124/count-case-and-when-statement-in-mysql — , Dec 15 '16 at 10:58
you group by a other value. please show us all values of p.value — Bernd Buffen, Dec 15 '16 at 11:00
no - sample : p.value = 'bar' is not the same like 'bars' in the group but in the case — Bernd Buffen, Dec 15 '16 at 11:09

score 4 · Answer 1 · answered Dec 15 '16 at 11:03

4

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

answered Dec 15 '16 at 11:03

sagi

40,026
6
59
84

While not fitting solution for my case, it's a valid answer as well, thanks! – Kadel Dec 15 '16 at 12:32

score 4 · Accepted Answer · answered Dec 15 '16 at 11:04

4

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

answered Dec 15 '16 at 11:04

Shadow

33,525
10
51
64

I was afraid this is the case. There's way too much data for union in my case, so I'll have to look for alternative solutions. Thank you. – Kadel Dec 15 '16 at 12:31
Take a look at the dynamic part of the accepted answer in this topic: http://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns – Shadow Dec 15 '16 at 13:05

score 3 · Answer 3 · answered Dec 15 '16 at 11:57

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  
;

answered Dec 15 '16 at 11:57

David דודו Markovitz

42,900
6
64
88

concat appears to have some collation issues for me, I'll investigate. Thanks! – Kadel Dec 15 '16 at 12:58
Can you please add your table DDL? Thanks – David דודו Markovitz Dec 15 '16 at 13:02

score 2 · Answer 4 · answered Dec 15 '16 at 11:09

2

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

answered Dec 15 '16 at 11:09

nimour pristou

153
1
8

1

Interesting solution to put it in rows. I'll look into that. – Kadel Dec 15 '16 at 12:50

Count multiple CASE occurrences in SQL

4 Answers4