[thelist] SQL: using GROUP BY and COUNT
rudy
r937 at interlog.com
Tue Aug 20 12:44:00 CDT 2002
>> 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
More information about the thelist
mailing list