Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    11

    Unanswered: condition on count

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Use HAVING. Like this....

    select city, count(*) from table1 group by city HAVING count(*) > 1000
    Last edited by tonkuma; 12-29-09 at 06:07.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Nov 2009
    Posts
    11

    Thanks a lot

    The question is answered

  5. #5
    Join Date
    Nov 2009
    Posts
    11
    Thank you Tonkuma and Knut Stolze for your replies.

    Regards,
    rpkulkarni

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •