[thelist] SQL: using GROUP BY and COUNT

[rudy]

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

provides convenient column alias

otherwise it is Expr1 or some such (depending on database)

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

count(*), like other aggregate functions, always refers to a group

the tricky part is, when GROUP BY is omitted, then the entire result set is
a (single) group

for example,

 select max(dateofbirth)
   from employees

will treat the entire employees table as one group and give you the max()
for that whole group

>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

ah, you want LIKE, not LEFT

your original example had all the search strings at the left   ;o)

what was the zrFromWhere column for?  referer ip number?

i've left it in, whatever it is

what's the name of the column the search string has to be found in?

i've called it foo

SELECT zrSearchString, zrFromWhere, count(*)
FROM ZeroResults
WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02#
AND  foo LIKE '*' & zrSearchString & '*'
AND zrSearchString   IN ('larry','curly',moe')
GROUP BY zrSearchString, zrFromWhere
ORDER BY COUNT(*) DESC, zrSearchString

substitute the list of keywords to analyze in place of the three stooges

notice zrSearchString has asterisks concatenated around it, those are
msaccess wildcards, i assumed msaccess because of its weird date strings

rudy

p.s. your column names reminded me of

"What you are doing is trying to expose the physical storage choices in
your logical data model all over again. And you are making your code hard
to read. Try to read "Paris in the Spring," "nounParis prepIn artThe
nounSpring" and see if the prefixes make it easier to understand; now
imagine that was a 20 word sentence with subclauses."
    -- Ten Things I Hate About You by Joe Celko.
http://www.intelligententerprise.com/001205/celko1_1.shtml