[thelist] SQL: using GROUP BY and COUNT

[Chris W. Parker]

> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
> Sent: Tuesday, August 20, 2002 10:28 AM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL: using GROUP BY and COUNT
>
>
> >>    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

oh, i get it.

> > 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

i see. i read about this recently while looking for information on the
subject.

>
> 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

thanks a lot rudy!

chris.