[thelist] SQL: using GROUP BY and COUNT

rudy r937 at interlog.com
Mon Aug 19 22:54:01 CDT 2002


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

not quite

it's constructing a column and sticking 'goggle' in it for every row
returned by the query, in this case one

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

   SELECT 'goggle', COUNT(*)
   FROM ZeroResults
   WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02#
   AND zrSearchString LIKE '*goggle*'
 UNION ALL
   SELECT 'fibble', COUNT(*)
   FROM ZeroResults
   WHERE zrTimeAdded BETWEEN #1/1/02# AND #1/31/02#
   AND zrSearchString LIKE '*fibble*'
ORDER BY 2 desc

notice how there's only one ORDER BY, and it's sorting the combined results
on the count

(some databases let you use a column alias instead of the ordinal number 2
for the second column, but only if you declare the alias on the first of
the selects in the union)

since there are only two rows returned, this puts either the 'goggle' or
'fribble' row first, whichever had the highest count

three keywords, and it's three selects unioned together

to collapse these separate unioned queries into one general query that you
can pass a number of keywords to, would require a bit more legerdemain,
probably more than you're interested in at this point

running one query at a time, feeding in one keyword at a time, you would
still probably place the keyword into the select list, so that it appears
in the result set, just so that you don't have to show just a raw count --
often the search page, where you enter the keyword, is separate from the
results page, where you show the count, so it's sort of "passed along" with
the count

rudy




More information about the thelist mailing list