Thanks to all for the help (Arno, Alex and Helen)

I guess it was one of those Simpsons doh things.....:-)

Helen Borrie wrote:

> 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
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>

--
Rafael Szuminski
Email:raf@...
Phone:(949)939 - 2458
www.BDCSoftware.com