Trying to work out the SQL for.....

[Anon]

Right....

I have a table that has a keyword on each row (along with other data that is unimportant for this). A keyword can occur more than once. I have another table with a list of keywords, but each is unique.

I need to return the number of times a given keyword occurs in the first table that also exists in the second table. At the moment there are no keys or indices....

My main problem is not entirely understanding thew group functions I think....

Thanks
Oliver

[brandonschnell]

i'm not 100% sure, but try this:

select unique_table.keywords, count( keyword_table.keywords) from unique_table, keyword_table where unique_table.keywords = keyword_table.keywords group by unique_table.keywords;

[Anon]

Hope this example can help.

Table FUNCTIONS contains some function descriptions.
Each record in this table has LIBID field (a foreign key, not unique) pointing to a corresponding record in LIBRARIES table.

*** FUNCTIONS

Name Libid

AAA 4
AAB 1
AAC 2
AAD 10
ABA 4
ABB 1
ABC 4

Table LIBRARIES has same name field LIBID. In this table this field has UNIQUE values, which is important. This is the PRIMARY KEY for this table.
*** LIBRARIES

Libid Name

1 Lib1
2 Lib2
3 Lib3
4 Lib4

Notice: not all records in FUNCTIONS have corresponding records in LIBRARIES.
Actually this is a very bad situation, which shouldn't happen; though it is the common one.

Test the following SQL statement on these two tables.
In human language: <Em>for each library ID count number of functions in FUNCTIONS; include only library IDs that exist in LIBRARIES</Em>

SELECT
fn.libid, lb.name,
COUNT(fn.libid) As libcount
FROM
libraries As lb,
functions As fn
WHERE fn.libid = lb.libid
GROUP BY fn.libid

The statement returns resulting records:

libid libcount

1 2
2 1
4 3

Similar results can be achieved with JOIN instead of WHERE -- that's the classic way 🙂

SELECT
fn.libid, lb.name,
COUNT(fn.libid) As libcount
FROM
functions As fn
LEFT JOIN libraries As lb
ON fn.libid = lb.libid

GROUP BY fn.libid

Regards!
Anatoliy Mogylevets
[e-mail]