[thelist] SQL: using GROUP BY and COUNT

[Chris W. Parker]

> -----Original Message-----
> From: rudy [mailto:r937 at interlog.com]
> Sent: Monday, August 19, 2002 8:52 PM
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL: using GROUP BY and COUNT
>
>
> > 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

ok. now i get it.

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

yes i do notice. is the 2 saying "ORDER BY the second COUNT(*)"?

and what if i want to use 150 different words?

> 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

if you're up to showing me, i'm up to trying it. but at the same time i
know that access (actually i think it's Jet) is a bit limited in it's
SQL capabilities. so we'll have to see how far it can go.

i appreciate all your help so far rudy.

chris.