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