From: Tsubasa Tanaka
Date: June 17 2012 2:34pm
Subject: Re: distinct & count operation with the use of "where count > $num"
List-Archive: http://lists.mysql.com/mysql/227690
Message-Id: <CAHB5oTOMay4wF_A-gcbVYgjTDzS_MU3X3HPPpYhEX_tFHcLFAw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

you have to use `HAVING' instead of `WHERE' like this.

SELECT DISTINCT
 `term`,
 COUNT(*) AS count
FROM blp_sql_distinct_temp_table
GROUP BY `term`
HAVING count >=3D 5
ORDER BY count DESC;

put `HAVING' next of `GROUP BY'.

`WHERE' behaves at before aggregate of `GROUP BY'.
your SQL means like
SELECT .. FROM (SELECT * FROM .. WHERE count >=3D 5) AS dummy GROUP BY ..
because of that, mysqld says `Unknown column .. in where clause'

regards,

2012/6/17 Haluk Karamete <halukkaramete@stripped>:
> Hi, I'm trying to get this work;
>
> SELECT distinct `term`,count(*) as count FROM
> blp_sql_distinct_temp_table where count >=3D 5 group by `term` order by
> count DESC
>
> But I get this error;
>
> Unknown column 'count' in 'where clause'
>
> How do I get only those records whose group by count is above 5?
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql
>