At 04:27 PM 11/03/2004 -0800, you wrote:

>Hi All,
>
>I have a query that has me stomped, to be exact it's the HAVING clause :-)

Exactly right. See Alexandre's reply.

>SELECT
> ZIPCODE_ANALYSIS.ACTIVITY_NAME AS ActivityName,
> ZIPCODE_ANALYSIS.ZIP AS CustomerZip,
> COUNT(ZIPCODE_ANALYSIS.C_ID) AS CountCID
>FROM ZIPCODE_ANALYSIS
>GROUP BY
> ZIPCODE_ANALYSIS.ACTIVITY_NAME,
> ZIPCODE_ANALYSIS.ZIP
>HAVING
> (ZIPCODE_ANALYSIS.ACTIVITY_DATE BETWEEN '02/01/2004' AND '02/29/2004')
>ORDER BY 1
>
>
>Can anybody explain to me what the hell is going on?

In your example, the HAVING clause can't do anything at all because its
search criteria are not part of the output.

Use a WHERE clause for search criteria. Use a HAVING clause to limit the
output of aggregations. For example, in your case

...HAVING COUNT(ZIPCODE_ANALYSIS.C_ID) < 100

Something like
...HAVING ZIPCODE_ANALYSIS.ZIP = <something>
would be legal (logically) but it is wasteful, because HAVING operates on
output.

>Btw, I am using IB 6 and haven't fired up FB yet, but I assume that the
>results
>will be the same.

It would (rightly) cause an exception in Firebird 1.5) but IB 6 lets you do
some illogical things with GROUP BY. I think Fb 1.0 is less tolerant of
bad GROUP BY clauses than IB 6 but it still allows some illogical things.

/heLen