Search code examples
sqloraclecounthaving-clausesql-null

What is the difference between count(*) and count(2) in ORACLE

I'm looking at some SQL code and I'm trying to figure out what is the difference between count(*) and count(2) in oracle.

For Example

select id_number from person
where type_id = 0010
group by id_number
having count(2) > 1;
Solution

  • Both expressions produce the same result.

    count(<expr>) takes in account all non-null values of <expr>. 2 is a literal, non-null values, so all rows are taken into account, just like count(*) does.

    You could as well express this with count(1), count('foo'), or any other (non-null) literal value.

    I like count(*) better, because it is somehow clearer about what it does: every row is in the group is counted, regardless of the values it contains.