How To Get SELECT from this table???

[Anon]

I have a table where all the searches are beeing stored... but now how to get statistics from this table....
Table looks like this:

create table statistic_search (
stat_id INT NOT NULL AUTO_INCREMENT,
stat_hash VARCHAR (32) NOT NULL,
stat_keyword VARCHAR (255) NOT NULL,

stat_ip VARCHAR (16) NOT NULL,
stat_date DATETIME NOT NULL,
PRIMARY KEY (stat_id)
);

How to get for example ALL or 10 most searchable (with the highest count(*) ) keywords.. keywords are beeing stored in stat_keyword...
for example we have 10 records with stat_keyword = "product", 5 with "firm", and 1 with "mp3".
How to get as a result (may be not exactly from SELECT but after some functions or sth) sth like this...

product 10
firm 5
mp3 1

THX

[Anon]

Two examples

1:

a) Get list of UNIQUE keywords:

"SELECT DISTINCT stat_keyword FROM statistic_search"

b) Counting records (for each keyword):

"SELECT COUNT(*) FROM statistic_search WHERE stat_keyword=:keyword"

2:

SET TERM ^ ;

CREATE PROCEDURE STATS
RETURNS (CNT INTEGER)
AS
DECLARE VARIABLE KEYWORD VARCHAR(255)
BEGIN
// Loop for each unique element
FOR SELECT DISTINCT stat_keyword FROM statistic_search INTO :KEYWORD DO
BEGIN
// Counting elements where keyword = :KeyWord
SELECT COUNT(*) FROM statistic_search WHERE stat_keyword=:KEYWORD INTO :CNT;
// Suspends execution (returns value) until next fetch is called
SUSPEND;
END
END
^

SET TERM ; ^

P.S.: SQL-code for PROCEDURE writing for InterBase and may not be compatible with other SQL-servers 🙁

P.P.S.: There are many ways to wrote this is single SELECT statment (but I don't remember how to make this)

P.P.P.S.: sorry for some (maybe more) erros in my English

[gio2k]

Simple:

SELECT stat_keyword, count(*)
FROM statistic_search
GROUP BY stat_keyword

(You can restrict even more, just check the documentation on SELECT... FROM, especially the HAVING clause)

Good luck!

-Gio-