COUNT(*) function

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 Monitoring 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 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;