Aggregates in Query Manager
Users can count employees, sum payroll dollars, find most recent paycheck date, and calculate other
aggregate values in Query Manager. Aggregates in Query Manager are similar to pivot tables in
Microsoft Excel: they allow users to quickly summarize data.
The available aggregate functions are Sum, Count, Minimum, Maximum, Average, and Count Distinct.
Query results can be restricted by aggregate values using the Having tab. Aggregates can also be used in
Expressions.
Procedure for Aggregating a Field
1.
Create your query with desired Records, Fields, and Criteria. The field you want to use as an
aggregate has to be a field in your query. For example, if you want to count employees, the
EMPLID field needs to be included in the query.
2.
Go to the Fields tab.
3.
Find the field that you want to summarize and click the Edit button on that row.
4.
On the Edit Field Properties page, in the Aggregate box, select the aggregate function you want
to use. The available options are Sum, Count, Min, Max, Average, and Count Distinct.
Aggregates in Query Manager PAGE 2
5.
Click OK at the bottom of the Edit Filed Properties page to return to the Fields tab. On the Fields
tab, the aggregate function is displayed in the column labeled Agg.
6.
Run the query. The output will be summarized.
Tip
Pay attention to the other (non-summarized) columns in the query. The report output will be one
row per unique combination of the other columns. For example, if you have only department and
you count employee IDs, the result will be one row per department. But if you add job title to the
query, the report will now show one row per department per job title.
Aggregates in Query Manager PAGE 3
Procedure for Filtering by an Aggregated Field
1.
Build the query with the aggregate field column.
2.
When you filter using aggregated fields, the restriction will be under the Having tab, not under
the Criteria tab. Otherwise, the two filters are similar: they have the same options for Condition
Type and Expression 2.
3.
Go to the Fields tab.
4.
Click the Add Criteria funnel icon for the aggregated field.
5.
On the Edict Having Criteria Properties page, select the Condition Type and Expression 2. Even
though Expression 1 has just the field name and not the aggregated expression, the restriction
will apply to the aggregated value.
6.
Click OK at the bottom of the Edit Having Criteria Properties page.
Aggregates in Query Manager PAGE 4
7.
The new restriction will be in the Having tab, not in the Criteria tab.
8.
Run the query. The report will only display rows where the aggregated value meets the
restriction.
Tip
You can also create an aggregate filter directly from the Having tab using the Add Having Criteria
button.
Procedure for Creating an Aggregate Expression
1.
Build the query with the aggregate field column. It's best to include all fields that will be part of
the expression as columns in your query.
2.
Go to the Expressions tab.
3.
Click Add Expression.
4.
On the Edit Expression Properties page, choose the Expression Type and fill in the Length.
5.
Click the checkbox for Aggregate Function.
Aggregates in Query Manager PAGE 5
6.
Type the expression into the Expression Text box. When you include the aggregated field, just put
in the record alias and the field name. Do not repeat the aggregate function; Query Manager will
automatically add it. For example, if A.EMPLID is aggregated with Count Distinct, you would type
"case when A.EMPLID > 10" and not "case when count(distinct A.EMPLID) > 10."
7.
Click OK at the bottom of the Edit Expression Properties page.
8.
On the Expressions tab, select Use as Field.
9.
On the Fields tab, rename the new field using the Edit button.
10.
Run the query. The expression is now visible as a column in the output.
Tip
You can filter based on an aggregate expression by clicking the Add Criteria funnel icon on the
Expressions tab or the Fields tab. This action will create a Having filter.
