Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369

    Unanswered: count(0) in the query

    I'd like to understand the reason why someone would use count(0) or count(some number) in the query. For example:

    db2 "select count(JOB) from staff"

    1
    -----------
    35

    1 record(s) selected.



    db2 "select count(0) from (select count(JOB) from staff)"

    1
    -----------
    1

    1 record(s) selected.



    db2 "select count(10) from (select count(JOB) from staff)"

    1
    -----------
    1

    1 record(s) selected.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by db2girl View Post
    I'd like to understand the reason why someone would use count(0) or count(some number) in the query.
    i think the main reason is to make you think

    the COUNT() function counts, right?

    and the main characteristic of how this function works is that it ignores NULL, yes?

    so COUNT(0) and COUNT(1) and COUNT(937) will all return the exact same results for any given query, and the answer that they will return is exactly the number of rows that the query produces

    the number of rows that the query produces, of course, depends entirely on which table(s) are involved, and whether there are any WHERE conditions

    now see if you can apply this to your sample queries which return 1

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I guess in olden days the reasoning might have been that count(1) would result in fewer I/Os than count(*), but optimizers are (now?) smart enough not to access unneeded columns, so that does not matter anymore.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Thank you all. I think I understand this now.

    I also found Rudy's article about count(*) - count(1)

Posting Permissions

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