Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    130

    Unanswered: How to do this SELECT statement

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2009
    Location
    Rutherford, NJ
    Posts
    9
    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.

  5. #5
    Join Date
    Dec 2008
    Posts
    135
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •