List how many records have each value in a field
Often you will want a list of how many records have each value in a table. For example:
- How many members of each ethnicity are in our database?
- How many families are in each family group?
- How many contributions came from check, credit card, cash, or other?
The following instructions assume that you already know how to use IQ. If you are not familiar with IQ, check out the Introduction to IQ Video or the article How Do I Get Started with ParishSOFT IQ?
To create a query that counts the total number of each value, go through the following steps:
- In Query Conditions, query for all the records you are looking for (example: all Active members, all registered families, all contributions).
- In Result Columns, add the column on which you want to report (example: ethnicity, family group, contribution type).
- In Result Columns, add the exact same column a second time, so that you see two identical result columns.
- Move the mouse over the second row. It will turn grey. There will be a button with an f to the right.
- Select the f button and choose "Count" from the menu that opens up. The second result column will update to say "Count of ..."
- Move the mouse over the first column. You will see an arrow button to the left.
- Select the arrow button and choose "Ascending". This will put the results in order.
- Click "Execute Query."
You will get a list of categories along with the number of records in that category.
Updated