This topic introduces you to "advanced" query design concepts. The concepts are considered advanced because they show you how to use some of ParishSOFT IQ's more complex operations, thereby advancing your knowledge of how to use the application to find answers to your questions.
Working with aggregate functions.
Using the result of one query in another query
Sometimes, you simply want a summary of the information in multiple records. For example, you might want to know how many registered families are in your organization or the average age of members in your organization. You can design a query that gives you this kind of summary information by using one of ParishSOFT IQ aggregate functions.
Aggregate functions are special operators applied to columns that return a single value. ParishSOFT IQ has five aggregate functions that you can apply to a column's data:
● Sum: adds all of the values in a column together and returns the sum of the numeric values.
● Count: counts the number of values in a column that meet the specified condition and returns a single integer value that represents the total number counted.
● Average: averages the values in a numeric column and returns the average value.
● Minimum: finds the lowest (or smallest) value in a column.
● Maximum: finds the highest (or largest) value in a column.
You can apply these functions to any column in a query without having to change the query's design. To learn about aggregate functions and see some examples how you can use them to answer a question, go to How to Calculate a Column's Data (Aggregate Functions).
Sometimes, your question is more complex than a simple query can handle, requiring you to design two queries to produce the information you desire. Depending on the type of question being asked, you can take the result from one query and use it in another query's where clause to find the answer you are looking for.
This topic illustrates how you can put this concept into practice: How to Use the Result of One Query in Another Query.
It is possible to embed one query inside of another query. This type of construct is called a "subquery".
To learn about subqueries and how to use them, go to How to Use Subqueries.
Another advanced concept is grouping conditions. Grouping conditions lets you perform more complex searches and check your database to find records that meet very specific conditions.
For information about combining conditions, go to How to Group Conditions.
How to Calculate a Column's Data (Aggregate Functions)
How to Use the Result of One Query in Another Query