How to do this SELECT statement

cy163 · #1 (**permalink**) 03-06-09, 01:59

Hello ALL.

I have a table shown below. It contains words from 3 documents. I would like to know word matching info between two document.Specifically, I would like to know the sum of occurrence frequencies of matching words, e.g. Doc1 and Doc2 have only one matching on 'English', and the sum of frequencies is 1+2=3.

Thanks

Code:

TABLE 

DocID  Word        Freq 
1      English      1 
1      Lesson      2 
1      Work        2 
1      Training    2 

2      English      2 
2      Have        2 
2      Class        2 
2      Student      3 


3      Lesson      3 
3      Chinese      2 
3      Chair        2

healdem · #2 (**permalink**) 03-06-09, 02:57

have a look at the MySQL manual for the following SQL clauses/terms
count
sum
group by

rather than provide the SQL I'd suggest doing it this waa round so you start to understand how SQL works

r937 · #3 (**permalink**) 03-06-09, 05:27

healdem, i guess you aren't familiar with cy163's previous posts

he's ~way~ beyond needing to look things up in da manual

he's been working on this app for months and months

where he needs help is understanding his data so that he can formulate the queries

aradapilot · #4 (**permalink**) 03-06-09, 16:56

select word, SUM(freq)
from table
group by word

thatll give you a list of all words with the total number of times they were used.

bklr · #5 (**permalink**) 03-07-09, 01:50

try this once

Code:

declare @tbl table(DocID int, Word  varchar(23),Freq int)
insert into @tbl select 
1 ,     'English',      1 
insert into @tbl select 
1,      'Lesson',      2 
insert into @tbl select 
1 ,     'Work',      2 
insert into @tbl select 
1,      'Training',      2 
insert into @tbl select 
2,      'English',      2 
insert into @tbl select 
2,      'Have',        2 
insert into @tbl select 
3,      'Lesson',      3 
insert into @tbl select 
3 ,    'Chinese',      2 

select s.word, SUM(s.freq)as freq
from @tbl s inner join @tbl t on t.word = s.word and t.docid <> s.docid
where s.docid in (1,2) and t.docid in (1,2)
group by s.word

Internet Services

Web Development

Digital Marketing

Consumer Tech