CNFS IQ - How to Use the Result of a Query in Another Query

Some problems require you to design more than one query to find a solution. This topic walks you through the process of how to use an aggregate value calculated as a result from one query as criteria (a condition) for another query. By combining the information from both queries, you can find the information you are looking for.


Question

Suppose you want to find out the following: Which families pledged more than the average amount to the All Souls fund?

Analysis

This single question actually consists of three embedded questions:

        The first question is What is the average amount pledged to the All Souls fund?

        The second question is Which families pledged to the All Souls fund?

        The third question is: Of the families who pledged to the All Souls fund, which pledged more than the average amount"?

To produce this information, you need two queries:

        Query 1 must determine the average amount pledged to the All Souls fund. This query returns one value that you can use in the design of the second query to produce the desired information.

        Query 2 finds the names of the families who pledged to the All Souls fund. It also uses the result from Query 1 to find the records of families whose pledges exceed the average amount.

Query 1: Finding the Average Pledge Amount to the All Souls Fund

This query enables you to find out average dollar amount of pledges to the All Souls fund.

  1. In the Column Picker panel, select these columns for the query:
  • Funds > Description
  • Pledges > Total Pledge Amount
  1. 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.
  2. To find the average pledge value, you need to apply the Average aggregate function to the Total Pledge Amount column. To do this, complete the following:

a. In the Result Columns panel, slide your cursor to the right of the column until you see this icon: . Click it to display the Functions menu:

b. Select Average.

The Average function is now applied to the column.

  1. In the Query Conditions panel, add this condition to the Funds Description column:

  1. Click  to run the query.

The query calculates the average value in the Pledges Total column and returns the single result. In our example, the query returns this value: $947.68.

Query 2: Finding the Names of Families Who Gave More Than the Average Pledge Amount

Now, that you know the value of the average amount pledged to the All Souls fund, you can use that value in Query 2 to find the records you are looking for.

  1. In the Column Picker panel, select these columns for the query:
  • Families > Last Name
  • Pledges > Total Pledge Amount
  1. 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.
  1. In the Query Conditions panel, specify these two conditions:

Note that you must type the average pledge value obtained from the Query 1 into the second condition's text field. By selecting the is greater than operator, you instruct the query to retrieve only those records having pledge amounts greater $947.68 (the average value).

  1. Click  to run the query.

In the example below, the result set provides the names of 15 families whose pledges to the All Souls fund exceeded the average amount. The specific pledge amounts are also listed so you can verify that each is greater than the average amount.

Have more questions? Submit a request

Comments

0 comments

Please sign in to leave a comment.