OSEC

Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email hr@neohapsis.com
Re: distinct & count operation with the use of "where count > $num"

From: Tsubasa Tanaka (yoku0825gmail.com)
Date: Sun Jun 17 2012 - 09:34:14 CDT

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 >= 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 >= 5) AS dummy GROUP BY ..
because of that, mysqld says `Unknown column .. in where clause'

regards,

2012/6/17 Haluk Karamete <halukkarametegmail.com>:
> Hi, I'm trying to get this work;
>
> SELECT distinct `term`,count(*) as count FROM
> blp_sql_distinct_temp_table where count >= 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:    http://lists.mysql.com/mysql
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql