Understanding Aggregate Functions

In a standard query, each row in the result set corresponds to an individual row in the table that you are querying. Sometimes, however, you instead want a summary of the information in multiple rows. For example, you might want to know how many customers you have in each state. You can query for this kind of summary information using aggregate functions.

An aggregate function is a special type of operator that returns a single value based on multiple rows of data. When your query includes one or more aggregate functions, PeopleSoft Query collects related rows and displays a single row that summarizes their contents.

For example, suppose that your Order table includes (among other fields) a customer ID and an amount for each item that was ordered. You want to determine how much each customer has ordered, so you create a query that selects the customer ID and amount fields. Without any aggregate functions, this query would return the same number of rows as are in the table. If Stuart Schumacher ordered 10 items, you would see 10 rows with his ID in the customer ID column. On the other hand, if you apply the aggregate function Sum to the amount field, you will get just one row for each customer ID. PeopleSoft Query collapses all the rows with the same value in the non-aggregated column (customer ID) into a single row. The value of the amount field in Stuart Schumacher’s row would be the sum of the values from the 10 rows.

The following table lists the aggregate functions that you can apply to a field using PeopleSoft Query.

Aggregate Function	Action
Sum	Adds the values from each row and displays the total.
Count	Counts the number of all rows in the query result including the null-value rows and duplicated rows.
Count Distinct	Counts the number of nonnull-value rows in the query result, and the duplicated rows are counted once.
Min (Minimum)	Checks the value from each row and returns the lowest one.
Max (Maximum)	Checks the value from each row and returns the highest one.
Average	Adds the values from each row and divides the result by the number of rows.