[thelist] SQL: using GROUP BY and COUNT

rudy r937 at interlog.com
Mon Aug 19 18:43:01 CDT 2002


> 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

besides, query results can be treated as though they were in arrays
already, we don't have to also "get" them or "dump" them, but i figure, why
handle a column as part of an array if you can refer to it **by name**

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>

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

> so then by this explanation i should get different
> COUNT values if i use * or zrSearchString? i'll try.

that's in general

exceptions:  there are no nulls, or the query is broken

yours is the second of those two exceptions   ;o)

but keep the general difference in mind for future queries

> 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

i am glad you started at the beginning with the simplest situation because
this will make it a lot easier to build up a more complex scenario

> 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

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)

'goggle' will have the same value in all rows

>SELECT zrSearchString, COUNT(*)
>FROM ZeroResults
>WHERE zrSearchString
>IN ('goggle','holster','gimbel')
>GROUP BY zrSearchString
>ORDER BY COUNT(*) DESC, zrSearchString

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

this returns the number of rows with 'goggle' somewhere inside
zrSearchString (for that date range)

you do *not* want to return all 689 different values of zrSearchString, i
assume

run this first query on a few different keywords, one at a time, until you
are convinced it's counting rows correctly, then i'll show you how to
search for two different keywords at the same time

rudy




More information about the thelist mailing list