Counting word occurrence with SQL query

Question

I have two tables.

Table1:

ID   SENTENCE
1    The shoes are good shoes.
2    There is a tree.
3    This is nice, nice, nice!

Table2:

ID   WORD
1    The
1    shoes
1    are
1    good
1    shoes
2    There
2    is
2    a
2    tree
3    This
3    is
3    nice
3    nice
3    nice

I need to count the occurrence of each word in every sentence from Table1. If any word occurs more than once (>1), then count it else skip it. In the end the resulting table should look like this:

ID   SENTENCE                   CNT
1    The shoes are good shoes.  2
2    There is a tree.
3    This is nice, nice, nice!  3

Answer 1

You can use count() over():

select distinct t1.id,
  t1.sentence,
  coalesce(t2.cnt, 0) cnt
from table1 t1
left join 
(
  select t1.id, 
    t1.sentence,
    t2.word,
    count(t2.word) over(partition by t1.id, t2.word) cnt
  from table1 t1
  left join table2 t2
    on t1.id = t2.id
) t2
  on t1.id = t2.id
  and t2.cnt > 1
order by t1.id

See SQL Fiddle with Demo.

Or you can just use count():

select t1.id,
  t1.sentence,
  coalesce(t2.cnt, 0) cnt
from table1 t1
left join 
(
  select t1.id, 
    t1.sentence,
    t2.word,
    count(t2.word) cnt
  from table1 t1
  left join table2 t2
    on t1.id = t2.id
  group by t1.id, t1.sentence, t2.word
  having count(t2.word) > 1
) t2
  on t1.id = t2.id
order by t1.id 

See SQL Fiddle with Demo