Working with 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.

When you apply an aggregate function to a field, you are redefining how PeopleSoft Query uses the field throughout the query. Essentially, PeopleSoft Query replaces the field, wherever it occurs, with the results of the function. If you select the field as a display column, PeopleSoft Query displays the aggregate values; if you use the field as an order by column, PeopleSoft Query orders the results based on the aggregate values.

Note: If you do not want PeopleSoft Query to redefine the field in this way—for example, if you want to display both the individual row values and the results of the aggregate function—create an expression that includes the aggregate function rather than applying the function directly to the field.

To apply an aggregate function to a field:

  1. Select Reporting Tools > Query > Query Manager.

  2. In Query Manager, select the Fields tab.

  3. Click the Edit button that is associated with the appropriate field.

    The Edit Field Properties page appears.

  4. Select the aggregate function that you want to use for this field, and click the OK button.

    The abbreviation for the selected function appears in the Agg (Aggregate) column. If a function is not available for the field that you have selected, the Edit Field Properties page does not close. For example, you cannot use Sum with a character field, but you can use Count, Min, and Max.

Note: In addition to the previously listed aggregate functions, you can use any aggregate function that is supported by the underlying database by creating an expression component.