COUNT(*) Function

The COUNT (*) function returns the number of rows that satisfy the WHERE clause of a SELECT statement. The following example finds how many rows in the stock table have the value HRO in the manu_code column:

SELECT COUNT(*) FROM stock WHERE manu_code = 'HRO'

The following example queries one of the System Management Interface (SMI) tables to find the number of extents in the customer table:

SELECT COUNT(*) FROM sysextents WHERE dbs_name = 'stores' AND tabname = customer"

You can use COUNT(*) as the Projection clause in queries of this general format to obtain information from the SMI tables. For information about sysextents and other SMI tables, see the IBM Informix Administrator's Reference chapter that describes the sysmaster database.

If the SELECT statement does not have a WHERE clause, the COUNT (*) function returns the total number of rows in the table. The following example finds how many rows are in the stock table:

SELECT COUNT(*) FROM stock

If the SELECT statement contains a GROUP BY clause, the COUNT (*) function reflects the number of values in each group. The following example is grouped by the first name; the rows are selected if the database server finds more than one occurrence of the same name:

SELECT fname, COUNT(*) FROM customer GROUP BY fname
   HAVING COUNT(*) > 1

If the value of one or more rows is NULL, the COUNT (*) function includes the NULL columns in the count unless the WHERE clause explicitly omits them.