[thelist] SQL: using GROUP BY and COUNT

[rudy]

>>    SELECT 'goggle', COUNT(*)
>>  UNION ALL
>>    SELECT 'fibble', COUNT(*)
>> ORDER BY 2 desc
>>
>> notice how there's only one ORDER BY, and
>> it's sorting the combined results on the count
>
> yes i do notice. is the 2 saying "ORDER BY the second COUNT(*)"?

no, it's saying sort on the 2nd column in the result set

> if you're up to showing me, i'm up to trying it.

you will need to build a table of keywords that you want to search on

a one-column table will do it, and google, fribble, etc., would be rows in
that table

then

    SELECT keyword, COUNT(*)
    FROM ZeroResults, keywordstable
    WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02#
    AND zrSearchString LIKE '*' + keyword + '*'
   group by keyword
    order by 2 desc

you are joining each row in ZeroResults with every keyword, and selecting
those keywords that are found inside zrSearchString

you have to delete and re-insert new keywords into the keyword table to run
different searches

rudy