Aggregate functions

An aggregate function returns one value for a set of queried rows. The aggregate functions take on values that depend on the set of rows that the WHERE clause of the SELECT statement returns. In the absence of a WHERE clause, the aggregate functions take on values that depend on all the rows that the FROM clause forms.

You cannot use aggregate functions for expressions that contain the following data types:
  • TEXT
  • BYTE
  • CLOB
  • BLOB
  • Collection data types (LIST, MULTISET, and SET)
  • ROW types
  • Opaque data types (except with user-defined aggregate functions that support opaque types)

Aggregates are often used to summarize information about groups of rows in a table. This use is discussed in Compose advanced SELECT statements. When you apply an aggregate function to an entire table, the result contains a single row that summarizes all the selected rows.

All HCL® OneDB® database servers support the following aggregate functions.