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 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 HCL OneDB™ 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.