PHP SQL Case when value like-CodePudding

I am trying to use case when to count the total when the column 'Name' has the value 'apple' in it.

So for example, I have 3 values in my column 'Name'.

hallo (apple), hallo today, hallo (apple)

I am trying to count the result which contains apple and I do not want it in the where part of the statement.

I am trying to accomplish this with the following statement, but I get a SQL error around the like operator. Is what I want possible and if so how do I do it?

SELECT 
COUNT(CASE WHEN Name = LIKE '%(apple)%' THEN 1 ELSE null end) AS TotalApples
FROM account WHERE id IN (0,$in)

I expect the result of this query to be 2 as the (apple) is 2 times present in my example.

CodePudding user response：

If you use the LIKE operator, you do not need the = operator

Hence, for your case please change

SELECT 
COUNT(CASE WHEN Name = LIKE '%(apple)%' THEN 1 ELSE null end) AS TotalApples
FROM account WHERE id IN (0,$in)

SELECT 
COUNT(CASE WHEN Name LIKE '%(apple)%' THEN 1 ELSE null end) AS TotalApples
FROM account WHERE id IN (0,$in)

Note: The clause WHERE id IN (0,$in) will only retrieve data where id is 0 or equal to $in. If this is not the case you want then please amend this where clause