Aggregate

QUEL Reference Guide > QUEL Reference Guide > Elements of QUEL Statements > Functions > Aggregate

Was this helpful?

Aggregate

An aggregate function returns a single value based on the contents of a column. Aggregate functions are also called "set" functions. Aggregate functions can be nested.

The syntax for QUEL aggregate functions is as follows:

afunct(expr [by expr{, expr}] [[only] where qual])

afunct

Denotes an aggregate function

expr

Donotes an expression representing a column or host variable

qual

Denotes a qualification. (Qualifications are explained below).

The following table lists aggregate functions:

Function	Data Type of Result	Value Returned
count()	i4	Number of entries in column
countu()	i4	Number of unique entries in column
sum()	i4, float8, money	Sum of values in column
sumu()	i4, float8, money	Sum of unique values in column
avg()	float8, money	Average of values in column
avgu()	float8, money	Average of unique values in column
max()	All types	Maximum value in column
min()	All types	Minimum value in column
any()	i2	Returns 1 if any rows satisfy the condition expressed by the argument; 0 if no rows satisfy the condition

Aggregate Functions Using the Where and By Clauses

Aggregate functions typically evaluate a column and return a single value (for example, avg(e.age) returns the average of all values in the "age" column of table "e"). This section describes how you can use the where and by clauses to modify the results returned by aggregate functions.

The where clause enables you to qualify (filter) the set of values used to determine the result of the aggregate function. For example,

sum(j.salary where j.salary > 1500

returns the sum of all salaries from table j that exceed 1500.

The by clause causes the function to return a set of results, as opposed to a single result. One result is returned for each grouping specified by the by clause. Think of by as meaning "for each." For example,

avg(e.age by e.dept)

returns an average age for each department in table e.

You can combine the by and where clauses:

avg(e.age by e.dept where e.job=1023)

returns the average age, by department, for employees who have a job code of 1023.

You can use the only where format to skip zero results. For example,

count(emp.salary by emp.dept where emp.salary > 10000)

returns a value for every department, but

count(emp.salary by emp.dept
only where emp.salary > 10000)

returns a value only when there are departments containing employees earning more than 10000.

If you use a by clause on a column that contains nulls, the DBMS Server returns a single result for the rows that contain null in the column specified in the by clause–in other words, nulls are grouped.

The result of the only where clause is affected by the set aggregate project|noproject statement. For more information, see SET--Set Session Options on page 79.

When an aggregate is applied to a nullable column, any nulls are disregarded in computing the aggregate. For example, for the following table "temp":

null

The statement

retrieve (c = countu(temp.x))

yields

Several variables can appear within a single aggregate function. For example,

avg(j.salary by e.dept where e.job=j.jid)

Last modified date: 11/28/2023