Progress
SQL-89
Guide and Reference


Using Aggregate Functions

Aggregate functions allow you to evaluate groups of values. In general, each function operates on the set of values in one column or on an expression. The set of column values is determined by the WHERE clause of a SELECT statement. Table 3–2 lists the SQL aggregate functions.

Table 3–2: SQL Aggregate Functions  
Function
Description
AVG( [ DISTINCT ] column )
Calculates an average value for all rows in the result list (or for all distinct rows if you so specify). The column must be numeric. It can refer to a column or it can be a calculation.
COUNT(*)
Counts the number of rows in the result list (this count includes duplicate rows).
COUNT(DISTINCT column )
Counts the number of rows containing different values for column.
MAX( [ DISTINCT] column )
Returns the maximum value of column for all rows (or for all distinct rows that you specify).
MIN( [ DISTINCT ] column )
Returns the minimum value of column for all rows (or for all distinct rows that you specify).
SUM( [ DISTINCT ] column )
Calculates the sum of column for all rows (or for all distinct rows that you specify). The expression must evaluate to a numeric value.

The following example counts all rows in the customer table.

SELECT COUNT(*) FROM Customer. 

You can include multiple aggregate functions in a single SELECT list. This example returns the average, maximum, and minimum price of items in the item table.

SELECT AVG(Price), MAX(Price), MIN(Price)
   FROM Item. 

When aggregate functions encounter null values, they are not included in the aggregate total, with the exception of COUNT(*). COUNT(*) counts all rows, including rows that have null values.

Aggregates of columns or expressions (except for COUNT) containing no values yield NULL (the Progress unknown value), rather than 0 as in the Progress 4GL. In the following example, because no customers have negative cust-num values, the aggregate function AVG yields a value of NULL for the credit-limit field.

SELECT AVG(Credit-Limit) FROM Customer
   WHERE Cust-Num < 0. 

The following example counts the number of different countries in the customer table and the total number of rows in the table.

SELECT COUNT(DISTINCT Country), COUNT(*)
   FROM Customer. 

Aggregate functions can include arithmetic expressions, and you can use the results of aggregate functions in other operations, as shown in the following example.

SELECT Cat-Page, SUM(Price * On-Hand)
  FROM Item
  GROUP BY Cat-Page
  HAVING SUM(Price * On-Hand) > 0
  ORDER BY 2. 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095