Using case when counting results from another table
I use this simple query to determine the most popular searches on my website:
select keyword, count(*) as total from searches group by keyword order by total desc limit 500;
I want to add a column that indicates how many times a particular keyword is found in testimonials. The testimonials are in a separate table called testimonies. I'm thinking that I need to implement the following, but I don't know exactly where:
count( case when (t.title like '%s.keyword%' or t.testimonyText like '%s.keyword%') then 1 else null end ) as countInTestimonials
So how do I modify the searches query to include a count in the testimonies table?