Hi,

> I have a query that has me stomped, to be exact it's the HAVING clause :-)
>
> 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

<snip>

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

results

> will be the same.

Firebird 1.5 would raised error message " Invalid expression in the HAVING
clause (neither an aggregate function nor a part of the GROUP BY clause)"
:-)
The HAVING clause is for conditions that apply after the aggregate grouping
and at that point ZIPCODE_ANALYSIS.ACTIVITY_DATE isn't grouped so what data
exactly is taken is unknown. In this case you must use the WHERE clause for
filtering. Besides that in the where clause a index (if available) could be
used for this query.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81