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)

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":

x |

0 |

1 |

1 |

2 |

null |

null |

The statement

retrieve (c = countu(temp.x))

yields

c |

3 |

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: 06/08/2023