OracleBlog

Quick, easy one, but it trips up beginners. Something of which to be mindful. Observe:

SELECT COUNT (*) FROM EMP;

  COUNT(*)
----------
        14

SELECT COUNT (COMM) FROM EMP;

COUNT(COMM)
-----------
          4

Note: 4 instead of 14. But if we do this:

SELECT COMM FROM EMP;
          
      COMM
----------

       300
       500

      1400




         0





14 rows selected.

We get all 14.

This is expected behaviour. From the Oracle SQL Reference: "If you specify 'expr' then COUNT returns the number of rows where 'expr' is not null."

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540.pdf

COUNT can take virtually any parameter, including 1, *, or a column. But be aware that if you're counting a particular column, NULLs won't be counted.

Additional reading material:

Everyone knows NULL is one of my favourite topics:
http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html

Eddie Awad on the difference between COUNT(*) and COUNT(1)
http://awads.net/wp/2005/07/06/count-vs-count1/
(Note: see the links in the comments for more information)