COUNT

The COUNT function counts only non-null values. Therefore, the data type of the result of the COUNT function always has the NOT NULL attribute. There are two uses of COUNT:

• COUNT(DISTINCT colname) -- Counts rows returned in which there is a non-null value in a named column. It eliminates duplicates from the count.

  This form must be used with a column name; it cannot be used with an expression. See also DISTINCT.

  SELECT COUNT(DISTINCT DIVISION)
  FROM Q.ORG

  The result is 4.

• COUNT(*) -- Counts rows returned regardless of the value of any column. This form is not used with a column name.

  SELECT SUM(SALARY), MIN(SALARY), AVG(SALARY),
     MAX(SALARY), COUNT(*)
  FROM Q.STAFF WHERE DEPT = 10

  This example includes more than one column function in the SELECT statement. It calculates and displays, for Department 10, the sum of employee salaries, the minimum, average, and maximum salary, and the number of employees (COUNT) in the department, and produces the following report:

  SUM(SALARY) MIN(SALARY)      AVG(SALARY) MAX(SALARY)
  ----------- ----------- ---------------- -----------
     83463.45    19260.25 20865.8625000000    22959.20
   
                                                COUNT(EXPRESSION 1)
  (Continuation of report)                      -------------------
                                                                  4