> -----Original Message----- > From: rudy [mailto:r937 at interlog.com] > Sent: Monday, August 19, 2002 3:06 PM > To: thelist at lists.evolt.org > Subject: Re: [thelist] SQL: using GROUP BY and COUNT > > > >> > 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) oh i see. in vbscript you use a function (or is it a property?) called GetRows(). this dumps the record set (or result set) to an array and is much quicker to cycle through the records. like so... rsZeroResults (this is the result/record set) rsZeroResultsArr = rsZeroResults.GetRows() that's basically how it works. > > 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 so then by this explanation i should get different COUNT values if i use * or zrSearchString? i'll try. > >> > 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's get rid of the zrFromWhere bit(s). i'm not using it just yet. ok so if $$keyword is 'goggle' then that SQL query would be... SELECT 'goggle', COUNT(*) FROM ZeroResults WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02# AND zrSearchString LIKE '*goggle*' ORDER BY COUNT(*) DESC this seems wrong because 'goggle' is not a column, so how can you SELECT it? for some reason i think i'm being unclear. let me show you the statement i've got as of now and then tell you what i would like to do. maybe that will get us back on track. i'm just getting confused. SELECT zrSearchString, COUNT(*) FROM ZeroResults WHERE zrSearchString IN ('goggle','holster','gimbel') GROUP BY zrSearchString ORDER BY COUNT(*) DESC, zrSearchString although this returns records for 'goggle' 'holster' and 'gimbel' it doesn't seem to return everything. let me try to explain. when i use this SQL statment... SELECT zrSearchString, COUNT(*) FROM ZeroResults WHERE zrTimeAdded BETWEEN #1/1/2001# AND #8/19/2002# GROUP BY zrSearchString ORDER BY COUNT(*) DESC, zrSearchString along with about 1k other records i get these... gimbel 634 gimbel glove 43 gimbel gloves 7 new gimbel glove 3 gimbel frisk & search glove 2 so that means that someone typed in "gimbel" 643 times and didn't find anything (this was before we had the gimbel product added). it also means that people typed in "gimbel glove" 43 times and "gimbel gloves" 7 times, etc. but when i run the first query that utilizes the IN ('goggle','holster','gimbel') statement i only get the "gimbel" 634 record returned. what i am expecting to get back is... gimbel 689 that is the five records above added together. what i think needs to happen is something like... IN ('goggle','holster','*gimbel*') ...using a wildcard. but that doesn't work and instead i think it is looking for "*gimbel*" instead of <WILCARD>gimbel<WILDCARD>. am i making sense? i tried LIKE IN, IN LIKE, IN (%'gimbel%'), IN ('%gimbel%'), etc. but to no avail. (i figured they wouldn't work, but i thought i'd give it a try anyway.) any ideas? > let me know if you want to do more than one at a time ok i will. yes i do. slightly confused, chris.