[thelist] SQL: using GROUP BY and COUNT

[rudy]

>> > does it serve another purpose?
>>
>> provides convenient column alias
>
>but you never reference the alias 'keyword' again

sure i do -- in the code that displays the result set   ;o)

> i'm still confused by this.
> maybe i should ask, why COUNT(*) and not
> COUNT(zrSearchString)?

good question, maybe worth a separate thread

count(*) counts rows and is more efficient, whereas count(colname) counts
non-null values -- in your case you wanted rows

>> what was the zrFromWhere column for?  referer ip number?
>
> that is where on the site the search was performed. "home page",
> "patrol", stuff like that. although it's not totally necessary it's got
to
> be helpful in some way to see where the popular search areas are
> and what people are clicking on the most.

beauty

>> > what's the name of the column the search string has to be found in?
>
> i'm not sure what you mean. if by search string you mean
> ('larry','curly','moe'), then the column those terms need to be
> found in is zrSearchString.

okay, then you don't want to group on it

i must admit i got turned around on that one -- i thought zrSearchString
was the keyword, not the target of the search


feed in your keywords one at a time from vbscript

i don't know vbscript so i'll use $$keyword to represent the string
(including its surrounding single quotes)

   SELECT $$keyword, zrFromWhere, count(*)
   FROM ZeroResults
   WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02#
   AND zrSearchString LIKE '*' & $$keyword & '*'
   GROUP BY zrFromWhere
   ORDER BY COUNT(*) DESC

let me know if you want to do more than one at a time

rudy