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

    Unanswered: how to query for words appearing in ONLY one doc

    Hello ALL,

    I have a table like
    Code:
    tlb
    
    ClassID  Class        Word           Freq
    1           Sports     basketball        3
    1           Sports     Football           2
    1           Sports     Volleyball         3
    
    2          Education  Basketball      2
    2          Education  Music             1
    
    3          Art            Picture           3 
    3          Art            Football          3
    I would like to find words which are unique for classes(i.e., words which appear only in one class), e.g.

    Code:
    Sports          Volleyball
    Education       Music
    Art              Picture
    I figure out the following query statement
    Code:
    SELECT ID, Class, Word FROM tlb GROUP BY Word HAVING COUNT(Class) = 1;
    but i am not sure. please help me.
    Last edited by cy163; 04-23-09 at 10:52.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SELECT Word FROM tlb GROUP BY Word HAVING COUNT(*) = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2007
    Posts
    130
    Thanks r937.

    i did not know what is the difference between COUNT(*) and COUNT(ColumnName) in a query statement. could you please explain this for me. Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    did you check da manual? it's pretty clear on the difference

    COUNT(*) counts rows

    COUNT(ColumnName) counts non-NULL values
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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