[thelist] SQL: using GROUP BY and COUNT
rudy
r937 at interlog.com
Mon Aug 19 17:07:09 CDT 2002
>> > 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
More information about the thelist
mailing list