Start a new topic

how to use count() function in where clause

[This topic is migrated from our old forums. The original author name has been removed]

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

[This reply is migrated from our old forums.]

Re: to solve it is in sql/plsql.
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
[This reply is migrated from our old forums. The original author name has been removed]

to solve it is in sql/plsql.
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.
[This reply is migrated from our old forums. The original author name has been removed]

Re: how to use count() function in where clause
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";
[This reply is migrated from our old forums. The original author name has been removed]

Re: how to use count() function in where clause
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
[This reply is migrated from our old forums.]

Re: how to use count() function in where clause
Hi, Generally I don't think you can use aggregate functions in the WHERE clause. Use HAVING (with GROUP BY) instead. select sender, count(reply_to) from lw_entries [b]having[/b] count(reply_to) >=1 Regards Roger
[This reply is migrated from our old forums. The original author name has been removed]

Re: how to use count() function in where clause

I think you can't use a where clause like that. The comparison using count should be in a having clause.

Regards,

Benny

[This reply is migrated from our old forums. The original author name has been removed]

Re: how to use count() function in where clause
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.



[This reply is migrated from our old forums. The original author name has been removed]

Re: how to use count() function in where clause

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
;