how to use count() function in where clause

hi guys,
 i am using db2 visualizer for my thesis.
 there is a query i wrote:

select sender, count(reply_to)
from lw_entries
where count(reply_to) > =1

the error message is :
 18:59:31  [SELECT - 0 row(s), 0.172 secs]  [Error Code: -120, SQL State: 42903]  Invalid use of an aggregate function or OLAP function.

can anyone tell me how to correct this query???

thankss a lot!!!!

hyphen81

I think the query below does what you're asking for. On any aggregation function you need to tell the query how you want the data grouped.

SELECT sender, count(reply_to) AS count_reply_to
  FROM lw_entries 
 WHERE count(reply_to) > =1
GROUP BY sender
;

hi mark,
thanks for the reply.
i guess the problem is not with group by, if it is with group by,  the erro message will be like " [Error Code: -119, SQL State: 42803]  An expression starting with "SENDER" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified."

i tried your query again, the error message still points to the count() expression.
does db2 visualizer sql commander have special rules for express count() in where clause?


SELECT sender, count(reply_to) AS count_reply_to
  FROM lw_entries
 WHERE count(reply_to) >= 1
GROUP BY sender

10:46:03  [SELECT - 0 row(s), 0.265 secs]  [Error Code: -120, SQL State: 42903]  Invalid use of an aggregate function or OLAP function.

Hi, Roger thanks for the tips:) it works with having clause, but in order to be 100% correct, the query should look like (include group by as well): select sender, count(reply_to) from lw_entries where project = 111 group by sender having count(reply_to) >=1 Regards! Hyphen

While [http://www.mindfiresolutions.com/using-having-clause-with-count-function-in-sql-533.php] on a query to count some rows based upon a condition I found an interesting thing.If you were to use the COUNT function in a query for a condition then we have to use the HAVING clause instead of WHERE clause. So the following query would return error: $wrong_query = "SELECT count( `fk_mp` ) FROM wrong_tab GROUP BY `pg_name` WHERE count( `fk_mp` ) > 1";

hello guys, how can solve this query ....... Q: display the all record of employee_id if records > 10. ( suppose record is > 10 ,then how will solve it. ) regards Nick.

Hi Nick, I'm sorry, but you need to try to look for an answer in some other forum. This forum is for questions regarding the use of the DbVisualizer product, not for general SQL questions. Best Regards, Hans