Recipe7.5.Counting Values in a Column

Recipe 7.5. Counting Values in a Column

Problem

You wish to count the number of non-NULL values in a column. For example, you'd like to find out how many employees are on commission.

Solution

Count the number of non-NULL values in the EMP table's COMM column:

  select count(comm)   from emp COUNT(COMM) -----------           4 

Discussion

When you "count star," as in COUNT(*), what you are really counting is rows (regardless of actual value, which is why rows containing NULL and non-NULL values are counted). But when you COUNT a column, you are counting the number of non-NULL values in that column. The previous recipe's discussion touches on this distinction. In this solution, COUNT(COMM) returns the number of non-NULL values in the COMM column. Since only commissioned employees have commissions, the result of COUNT(COMM) is the number of such employees.