How to check if a string contains a word at a case statement - SQLite

Question

This is my code:

SELECT 
CASE your_text
  WHEN contains('hate') THEN 'hater'
  WHEN contains('love') THEN 'lover'
  ELSE 'other'
END haterOrLover,
count(*) AS total_count
FROM a_table
GROUP BY haterOrLover

If only contains() existed!

I want to count how many haters, lovers there are and the result to be at this format:

+--------------+-------------+  
| haterOrLover | total_count |  
+--------------+-------------+  
| hater        | 1           |  
+--------------+-------------+  
| lover        | 2           |  
+--------------+-------------+  
| other        | 1           |  
+--------------+-------------+

Tried:

WHEN your_text like '%hate%' THEN 'hater'

WHEN '%hate%' THEN 'hater'

but didn't work.

How to check if a record contains a specific word at the 'your_text' column?

Edit
How to reproduce:

CREATE TABLE a_table
(
id         char(10)      PRIMARY KEY,
your_text  char(250)     NOT NULL
);

INSERT INTO a_table
    (id, your_text)
VALUES
    (1, 'I love dogs'),
    (2, 'I hate cats'),
    (3, 'I like rabits'),
    (4, 'I love haskis');

Thank you

---

Solution:

SELECT 
CASE
  WHEN your_text like '%hate%' THEN 'hater'
  WHEN your_text like '%love%' THEN 'lover'
  ELSE 'other'
END haterOrLover,
count(*) AS total_count
FROM a_table
GROUP BY haterOrLover

Answer 1

try it :

with cte as (
SELECT 
CASE when your_text like ('%hate%') THEN 'hater'
     when your_text like ('%love%') THEN 'lover'
     ELSE 'other'
     END haterOrLover FROM a_table )
select haterOrLover , count(*) as total_count from cte
group by (haterOrLover)