[thelist] SQL: using GROUP BY and COUNT

[Chris W. Parker]

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