Comparison of the Different COUNT Functions

You can use the different forms of the COUNT function to retrieve different types of information about a table. The following table summarizes the meaning of each form of the COUNT function.

Some examples can help to show the differences among the different forms of the COUNT function. Most of the following examples query against the ship_instruct column of the orders table in the demonstration database. For information on the structure of the orders table and the data in the ship_instruct column, see the description of the demonstration database in the IBM Informix Guide to SQL: Reference.

Examples of the COUNT(*) Function

In the following example, the user wants to know the total number of rows in the orders table. So the user calls the COUNT(*) function in a SELECT statement without a WHERE clause:

SELECT COUNT(*) AS total_rows FROM orders

The following table shows the result of this query.

In the following example, the user wants to know how many rows in the orders table have a NULL value in the ship_instruct column. The user calls the COUNT(*) function in a SELECT statement with a WHERE clause, and specifies the IS NULL condition in the WHERE clause:

SELECT COUNT (*) AS no_ship_instruct FROM orders
   WHERE ship_instruct IS NULL

The following table shows the result of this query.

In the following example, the user wants to know how many rows in the orders table have the value express in the ship_instruct column. So the user calls the COUNT(*) function in the projection list and specifies the equals ( = ) relational operator in the WHERE clause.

SELECT COUNT (*) AS ship_express FROM ORDERS
   WHERE ship_instruct = 'express'

The following table shows the result of this query.

Examples of the COUNT DISTINCT Function

In the next example, the user wants to know how many unique non-NULL values are in the ship_instruct column of the orders table. The user calls the COUNT DISTINCT function in the projection list of the SELECT statement:

SELECT COUNT(DISTINCT ship_instruct) AS unique_notnulls
   FROM orders

The following table shows the result of this query.

Examples of the COUNT column Function

In the following example the user wants to know how many non-NULL values are in the ship_instruct column of the orders table. The user invokes the COUNT(column) function in the Projection list of the SELECT statement:

SELECT COUNT(ship_instruct) AS total_notnullsFROM orders;

The following table shows the result of this query.

A similar query for non-NULL values in the ship_instruct column can include the ALL keyword in the parentheses that follow the COUNT keyword:

SELECT COUNT(ALL ship_instruct) AS all_notnulls FROM orders

The following table shows that the query result is the same whether you include or omit the ALL keyword (because ALL is the default).