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

How relates `table1` with `table2`? Is second just another representation of the first? — Hamlet Hakobyan, Feb 27 '13 at 11:05
this is not homework:) I could do this with bash&awk, but I need SQL which I am poor at:( — minerals, Feb 27 '13 at 11:16
the SENTENCE IDs of Table1 correspond to IDs of Table2. So a WORD "tree" with ID = 2, should be expected in SENTENCE with ID = 2. — minerals, Feb 27 '13 at 11:18

score 3 · Accepted Answer · answered Feb 27 '13 at 11:09

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

score 3 · Answer 2 · answered Feb 27 '13 at 11:19

SQL DEMO

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

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

score 0 · Answer 3 · answered Feb 27 '13 at 11:02

0

SELECT table1.id, table1.sentence, COUNT(word) as cnt FROM table2 JOIN table1 ON table1.id = table2.id GROUP BY table2.word HAVING COUNT(word) > 1

My answer is for mysql, I am verifying now that it works in sql as well

answered Feb 27 '13 at 11:02

James

2,013
3
18
31

1

I can't see `table1` in your query. – Hamlet Hakobyan Feb 27 '13 at 11:03

score 0 · Answer 4 · answered Feb 27 '13 at 14:26

There are many join examples, so, I will add only word count examples:

Select REGEXP_COUNT('The shoes are good shoes.', ' ')+1 words_count
 From dual
/

WORDS_COUNT
-----------
5

SELECT id
     , LISTAGG(word, ' ') WITHIN GROUP (ORDER BY id, word) AS words
     , count(*) word_cnt
  FROM your_table2
GROUP BY id
/

ID    WORDS                    WORD_CNT
---------------------------------------
1    The are good shoes shoes    5
2    There a is tree             4
3    This is nice nice nice      5

Counting word occurrence with SQL query

4 Answers4

Linked