Important Notice: This article applies to the New ParishSOFT Platform. If your diocese hasn’t migrated yet, explore our knowledge base for relevant resources.
To extract data from the database and calculate it. Aggregate functions (sums, counts, and averages) enable IQ to calculate and summarize data in queries. This article explains what an aggregate function is and shows how to apply this type of function to data.
What Is an Aggregate Function?
To aggregate means to put together or combine. An aggregate function, therefore, performs a calculation on the values in a column and returns a single (combined) value, such as a count or an average.
Aggregate Functions Menu
Five aggregate functions are available in ParishSOFT IQ. These functions are located on a special Functions menu ():
How to add an aggregate function to a column's data
You can assign an aggregate function to any column in the Results Column panel. To assign a function to a column:
Position your cursor on top of the column you want to assign the aggregate function to.
Drag your cursor to the right until you see this icon, and then click it: to display the Functions menu:
Select the desired function.
The function is added to the column name and title to let you know that it will be applied when you run the query. For example:
How to remove an aggregate function from a column's data
To remove an aggregate function from a column, drag your cursor to the right of the column until you see this icon, and then click it: .
The function is removed from the column name and title to let you know that it is no longer being applied.
Example 1: Count:
Question:
How many registered families live in the 48108 zip code?
Analysis:
For this question, you are looking for a single value: the total number of registered family records. You can obtain the answer by applying the Count aggregate function to the Families > is Registered column. Because you also want to limit the records retrieved to those of registered families in the 48108 zip code are, you need to apply a condition to this column to specify the zip code: Families > Addresses > Home PostalCode.
Query:
The following procedure shows you how to set up a query to answer the question.
In the Column Picker panel, select Families > is registered.
Click and drag the is Registered column into the Result Columns panel, making sure that you position the column on the [Add new column] link. Then, release the mouse button.
To find the number of records in the is Registered column, you need to apply the Count aggregate function to it. To do this, follow these steps:
In the Result Columns panel, slide your cursor to the right of the is Registered column until you see this icon: . Click it to display the Functions menu:
Select Count.
The Count function is now applied to the column.
To fnd records for families in the 48108 zip code area only, add this condition in the Query Conditions panel:
Families. Addresses.Home PostalCode is equal to 48108.
To ensure that the query retrieves unique records only, select the Unique Records Only link.
Click Execute Query to run the query.
The query adds up the number of records in the is Registered column returns a single value. As shown in the example below, the query determines that the number of registered families in the 48108 zip code area is 6480:
Example 2: Average
Question:
What is the average amount pledged to the Building Fund Campaign fund?
Analysis:
For this question, you are looking for a single numeric value: an average of the dollar amounts in the Pledges > Pledge Total Amount column. The query must add up the values in this column and then calculate the average dollar amount. The query also applies a condition to Funds > Description column to retrieve records for the Building Campaign Fund only.
Query:
The following procedure shows you how to set up the query to answer this question.
In the Column Picker panel, select Funds > Description and Pledges > Total Pledge Amount.
Click and drag each column into the Result Columns panel, making sure that you position the column on the [Add new column] link. Then, release the mouse button.
To find the average value in the Pledges Total column, you need to apply the Average aggregate function to it. To do this, complete the following:
In the Result Columns panel, slide your cursor to the right of the Pledges Total column until you see this icon: . Click it to display the Functions menu:
Select Average.
The Average function is applied to the Pledges Total column.
To limit the records to the Building Fund Campaign fund, add this condition: Funds Description is equal to Building Fund Campaign.
To retrieve unique records only, select the Unique Records Only link.
Click Execute Query to run the query.
The query calculates the average value in the Pledges Total column and returns the single result. As shown below, the query determines that the average amount pledged to the Building Fund Campaign fund is $1675.00:
Example 3: Maximum
Question
What is the age of the oldest member in our organization?
Analysis
For this question, you are looking for a single result (age of the oldest member), which you can obtain by applying the Maximum aggregate function to the Members > Age column.
Query
The following procedure shows you how to set up the query to answer this question.
In the Column Picker panel, select Members > Age.
Click and drag the Age column into the Result Columns panel, making sure that you position the column on the [Add new column] link. Then, release the mouse button.
To find the highest value in the Members Age column, you need to apply the Maximum aggregate function to it. To do this, follow these steps:
In the Result Columns panel, slide your cursor to the right of the Age column until you see this icon:
Click it to display the Functions menu:
Select Maximum.
The Maximum function is now applied to the column.
Click Execute Query to run the query.
The query calculates the maximum value in the Age column and returns the single result. As shown in the example below, the query determines that the oldest member in the organization is 118 years old:
Updated