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.

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

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.

Thank you all. I think I understand this now.

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