[thelist] SQL: using GROUP BY and COUNT

Chris W. Parker cparker at swatgear.com
Fri Aug 16 17:02:01 CDT 2002


hi.

(vbscript/access)

i've got a script that sends every unfruitful search term on a website
to a database. the database now contains nearly 300k entries and is
33.8mb big.

here is what i am using right now as an SQL query:

SELECT zrSearchString, COUNT(zrSearchString), zrFromWhere
	FROM ZeroResults
	WHERE zrTimeAdded
		BETWEEN #1/1/02# AND #1/31/02#
	GROUP BY zrSearchString, zrFromWhere
	ORDER BY COUNT(zrSearchString) DESC, zrSearchString

i'm grouping by (as you can see) the search string entered. that means
that if there are three entries...

goggles
goggles 2s
goggle's

...there is going to be three entries. what would it take query wise or
software wise to be able to GROUP BY "goggle" and other keywords so that
anything including "goggle" would be GROUP'ed. i'm also using the WHERE
BETWEEN clause so that i can limit the results from about 5k to more
like 400 records or so.


thanks for listening! err... reading.

chris.



More information about the thelist mailing list