Hello DB2 gurus,

I am really new to DB2 and SQL programming and facing problems in small-small issues like mentioned below. Kindly help me in resolving the issue.

I have person and city as two columns in the table1 and I want to get cities with count(person) > 1000.
The query is like this :
"select city, count(*) from table1 where count(*) > 1000 group by city"
But I cannot write condition like this in where clause.
How to write this where clause correctly?

Thanks,
rpkulkarni

Use HAVING. Like this....

select city, count(*) from table1 group by city HAVING count(*) > 1000

Have a look at the sequence how SQL statements are processed. You will find that a WHERE clause is applied to the values of a single row only. Thus, having an aggregation function there doesn't make any sense. HAVING clauses (as tonkuma suggested) are applied after any GROUP BY clause and apply to a whole group of rows. (If there is no GROUP BY clause, you have implicitly a single group containing all rows resulting from the SELECT ... FROM ... WHERE steps.)

The question is answered

Thank you Tonkuma and Knut Stolze for your replies.

Regards,
rpkulkarni