SELECT a, COUNT(a) AS b FROM tbl WHERE b > 1 GROUP BY a
should be
SELECT a, COUNT(a) AS b FROM tbl GROUP BY a having b > 1
where clauses cannot act on aggregate functions (count, sum, avg, etc).
to filter on aggregate functions you must use the having clause
HTH,
Peter
On 27 Mar 2003 at 17:17, Mike Starke wrote:
> How do you structure a sql statement such as
>
> SELECT a, COUNT(a) AS b FROM tbl WHERE b > 1 GROUP BY a
>
> I am looking for only the records which report
> a count greater than one. MySQL complains that column 'b' does
> not exist.
>
> -Mike
> ---------------------------------------------------
> PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change you mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
(text/plain)