Count where Like

Transact-SQL
1

Hi,

I'd need to make a query where I get as a result two columns. One column is directly from one table, but the other one is a hit count from another column. The number of hits is the number of WHERE LIKE (thefirstcolumn).

So if we take this with a very simple example:
Persons table:
(name)
Mary
John
Mike

Sentence table:
(sentence)
Mary had a lamb
John is a name
Mary and John are married

so the result from there should be like
(Name) (HitCount)
Mary 2
John 2
Mike 0

I tried like this:
select Persons.name, count(Sentence.sentence) as hitcount
from Persons, Sentence
where Sentence.sentence like persons.name

but obviously I'm missing an aggregate function there.

2 
select p.name
      ,count(s.sentence) as hitcount
  from Persons as p
       left outer join Sentence as s
                    on s.sentence like '%'+p.name+'%'
 group by p.name
2 Likes