how to query for words appearing in ONLY one doc

cy163 · #1 (**permalink**) 04-23-09, 09:39

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.

r937 · #2 (**permalink**) 04-23-09, 10:22

SELECT Word FROM tlb GROUP BY Word HAVING COUNT(*) = 1

cy163 · #3 (**permalink**) 04-23-09, 19:11

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.

r937 · #4 (**permalink**) 04-23-09, 19:21

did you check da manual? it's pretty clear on the difference

COUNT(*) counts rows

COUNT(ColumnName) counts non-NULL values

Internet Services

Web Development

Digital Marketing

Consumer Tech