[thelist] SQL: using GROUP BY and COUNT

[Chris W. Parker]

> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
> Sent: Monday, August 19, 2002 4:41 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL: using GROUP BY and COUNT
>
>
> > GetRows(). this dumps the record set (or result set) to an array and
> > is much quicker to cycle through the records. like so...
>
> if you would like to compare languages, mine is coldfusion, and we are
> taught not to dump stuff into arrays if there's no good
> reason to do so

must be different in asp. i'd like to hear from someone else.


> what i was talking about was the value of having a piece of
> code that says
>
>     <td>#temperature#</td>
>
> rather than
>
>     <td>#expression3#</td>
>
> or even your implied
>
>     <td>#queryresult[3]#</td>

ahhh... right. afaik asp doesn't work like that.

> you know, in those situations when you come back a few months
> later and
> can't remember which calculation went into the 3rd column, a
> column alias
> is much nicer

i see your point.


> > this seems wrong because 'goggle' is not a column,
> > so how can you SELECT it?
>
> on the contrary, 'goggle' is most definitely a column in the
> result set
> table

so SELECT 'goggle' is not saying "look for the string 'goggle' in ..."
(since it's not specify another column in the SELECT clause) but rather
it's saying, put the results in a column called 'goggle'. is that right?

> it will get a default column name if you don't alias it (some
> databases use
> a made-up column name like Expr1 while others will actually
> use the word
> goggle as the column name)

i see.

> no, that's completely backwards, if zrSearchString is supposed to
> **contain** those keywords
>
> you want
>
>   SELECT 'goggle', COUNT(*)
>   FROM ZeroResults
>   WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02#
>   AND zrSearchString LIKE '*goggle*'
>   ORDER BY COUNT(*) DESC

ok. in that time period when searching for EVERYTHING i get two
records...

"goggle's extra", 6
"goggles forever", 3

when i use your query i get...

"goggle",9

which is, i think, what i want.

so far i think we're on the right track.


i think i am ready for more than one keyword now! ;)



chris.

p.s. going home. continue tomorrow.