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