How to Calculate a Column's Data (Aggregate Functions)

Sometimes you need to extract data from your database and calculate it. Aggregate functions (for example, sums, counts, and averages) enable you to calculate and summarize data in your queries. This topic explains what an aggregate function is and shows you how to apply this type of function to your data.

What Is an Aggregate Function?

Aggregate Functions Menu

Adding an Aggregate Function to a Column's Data

Removing an Aggregate Function from a Column's Data

Examples of Queries Using Aggregate Functions

Count Function

Average Function

Maximum Function

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. Examples of using this type of function are as follows:

●        Finding the sum (total) values in a column. Example: the total amount contributed to a specific fund.

●        Obtaining a count of a set of records. Example: the total number of 18-year-old members in an organization.

●        Determining the average value in a column. Example: the average dollar amount of pledges made to a specific fund.

●        Finding the lowest value in a column. Example: the lowest contribution amount made to a specific fund.

●        Finding the highest value in a column. Example: the oldest member in your organization.

In each example, all you want is a summary of the column's data—not the column's actual data. Therefore, the query result is always equal to 1.

Aggregate Functions Menu

Five aggregate functions are available in ParishSOFT IQ. These functions are located on a special Functions menu ():

The following table lists and describes the aggregate functions.

Adding 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, do the following:

1. Position your cursor on top of the column you want to assign the aggregate function to.

2. Drag your cursor to the right until you see this icon, and then click it: to display the Functions menu:

3. 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:

Removing 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.

Examples of Queries Using Aggregate Functions

This section gives you examples of queries using these aggregate functions:

●        Count

●        Average

●        Maximum

Count Function: Counting the Number of Values in a Column

The most commonly used aggregate function is the Count function. Following is an example of how you might use this function.

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.

1. In the Column Picker panel, select Families > is registered.

2. 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.
3. 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:

1. 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:

2. Select Count.

The Count function is now applied to the column.

4. To find 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.

5. To ensure that the query retrieves unique records only, select the Unique Records Only link.
6. Click 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:

Average Function: Finding an Average Value

The Average function is used to find the average value of a certain column for a selected group of records. The column's value must be numeric for the Average function to work.

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.

1. In the Column Picker panel, select Funds > Description and Pledges > Total Pledge Amount.
2. 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.
3. 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:

1. 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:

2. Select Average.

The Average function is applied to the Pledges Total column.

4. To limit the records to the Building Fund Campaign fund, add this condition: Funds Description is equal to Building Fund Campaign.
5. To retrieve unique records only, select the Unique Records Only link.
6. Click 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:

Maximum Function: Finding a Maximum Value

The Maximum function is used to find the highest value in a certain column for a selected group of records.

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.

1. In the Column Picker panel, select Members > Age.
2. 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.
3. 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:

1. 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:

2. Select Maximum.

The Maximum function is now applied to the column.

4. Click 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:

Related Topics

How to Create a New Query

<Back to top>