CODEWITHSUNDEEP
sql
boisterouslobster
boisterouslobster

Reputation: 1293

Select count of LIKE terms

Given a mysql query such as this:

SELECT COUNT(*) FROM questions
WHERE questions.text LIKE "%some word%"
OR questions.text LIKE "%another word%"
OR questions.text LIKE "%yet another word%"
AND questions.field = "test"
... more WHERE conditions

What is the best way to return the breakdown for each LIKE term, rather than the rows which correspond to all three LIKE statements?

I know it is possible to do so by having separate SELECT statements for each of the LIKE terms, but is there any way to wrap this up into one SELECT statement, or something that might be a bit shorter?

Upvotes: 1

Views: 328

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You could use a select case when with the sum of matching values

SELECT 
    sum( case when questions.text LIKE "%some word%"  then 1 else 0 end) as  test1
  , sum( case when questions.text LIKE "%another word%"  then 1 else 0 end) as  test2
  , sum( case when questions.text LIKE "%yet another word%"  then 1 else 0 end) as  test3
WHERE questions.field = "test"
 ... more WHERE conditions

Upvotes: 1

Related Questions