[thelist] SQL: using GROUP BY and COUNT

[Chris W. Parker]

> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
> Sent: Friday, August 16, 2002 9:20 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL: using GROUP BY and COUNT
>
>
> >goggles
> >goggles 2s
> >goggle's
> >
> >... what would it take ... to be able to GROUP BY "goggle"
> >  and other keywords so that anything including "goggle"
> > would be GROUP'ed
>
> quick question, chris -- obviously if you feed in a specific
> keyword like
> "goggle" then you'd get only one group back, right?  so you
> aren't feeding
> in the keywords, what you must want is just to group by the leftmost N
> columns, where N is some number like 6?

yes you're right. i am not searching specifically for "goggle" or any
other set of keywords. i am just GROUPing BY the zrSearchString column.
i think what you are saying is what i meant to say. ;)

i will try out your suggestion.

> if that's the case, then
>
> SELECT Left(zrSearchString,6) as "keyword"
>      , COUNT(*)
>      , zrFromWhere
>   FROM ZeroResults
>  WHERE zrTimeAdded
>        BETWEEN #1/1/02# AND #1/31/02#
> GROUP BY Left(zrSearchString,6), zrFromWhere
> ORDER BY COUNT(*) DESC
>      , Left(zrSearchString,6)

in the meantime though...

why do you 'AS "keyword"' if you never reference "keyword" again? does
it serve another purpose?

why COUNT(*)? does that mean 'COUNT how many records are returned' or
does that mean to 'COUNT the number of GROUP'd records'? for example...

if i have these records...

goggle xp
goggle 2000
goggle's
eye goggles
2000 goggle's
holster
holster's
holsters

i want to get...

"goggle",5
"holster",3

...returned. i have two records with two values for each record. but
saying Left(string,n) will only match "holste" from "holster" right and
so instead i would be left with...

"goggle",3
"holste",3
"eye go",1
"2000 g",1

...which isn't very clear.

i've become confused!!

now that i think about it, i think i *do* actually want to feed a
keyword list. so that i can say "out of 50,000 searches the keyword
'goggle' was found in 27,456 separate searches."

this way i can rank (by using a long list or a short list of keywords)
the most popular keyword (and the least popular) in order of how many
times a keyword was searched for.

does this make sense?


chris.