Use Bind Variables and Aggregate Functions

Learning Objectives

After completing this unit, you'll be able to:

• Make a query dynamic by inserting Apex bind variables.
  
• Use an aggregate function to roll up and summarize data in a query.
  
• Apply grouping to query results.
  
• Recognize when to use the WHERE clause vs. the HAVING clause.
  

Follow Along with Trail Together

Want to follow along with an expert as you work through this step? Take a look at this video, part of the Trail Together series on Trailhead Live.

(This clip starts at the 1:10:42 minute mark, in case you want to rewind and watch the beginning of the step again.)

Introduction

We have discussed how to write a basic SOQL query and how to formulate queries that include fields in parent or child objects. But, as you know, each application development project is unique. Knowing how to tailor business logic to solve a specific problem is important.

Bind Variables

Using bind variables is one way to tailor your code. It's similar to opening a form in the user interface and modifying an input field to search for certain results. Let's look at an example in the DreamHouse app. You're a realtor selling houses, and you want to review your inventory of properties priced under $200,000. So you write this query:

List<Property__c> property = [SELECT Id, Name, Price__c FROM Property__c WHERE Price__c < 200000];

That's pretty simple—for you. But it's static. This query always returns houses under $200,000. What about other realtors and potential buyers who can't write SOQL queries and run them in the Developer Console? They need an easier way to filter houses by the price that's right for them. How can you write one query that works for a buyer whose budget is $200,000 and another buyer whose budget is $800,000? That's where bind variables come in. A bind variable is an Apex variable that you use in a SOQL query. Use bind variables as placeholders for specific values to be provided later.

This isn't a new concept. In Object-Oriented Programming for Admins, you learned about using a parameter as a placeholder in a method. When you call the method, you pass in an argument that replaces the parameter. A bind variable in a query is like a parameter in a method. The bind variable is replaced by a specific value when the query runs.

Consider a bind variable named maxHomeValue. We declare the variable in Apex and then use the variable in a query, like this:

Integer maxHomeValue = 200000;
List<Property__c> property = [SELECT Name, Price__c FROM Property__c WHERE Price__c < :maxHomeValue];

Notice that in the query, the bind variable is preceded by a colon ( : ). This query returns the name and price from the property object. But the results are filtered based on the price of the home: WHERE Price__c < :maxHomeValue. The value of the bind variable, maxHomeValue, is set outside the query. Let's look at this in action.

1. In your Trailhead Playground, click , type Dream in the Search box, and then select the DreamHouse app.
   
2. Click Property Explorer.
   The Property Explorer searches for available properties based on a number of filters such as a maximum price range. The Max Price slider accepts values for the maxHomeValue bind variable.
   
3. Move the Max Price slider to 550K.
   

Each time the slider moves, its associated value is modified in the code through its bind variable. The query runs and adds the results to a list of properties displayed on the property explorer page.

Using bind variables in the WHERE clause allows you to set variable values in your code before the query runs. Bind variables make a query dynamic. To customize a query for each user, set bind variable values based on user input. As you continue modifying and reviewing code, you'll discover many types of fields whose data is constantly changing.

Aggregate Functions

In reports, you use roll-up summary fields to calculate values from related records. For example, you might want to calculate the total number of properties, or the average or highest value of properties in a city. In SOQL, you use aggregate functions to perform those and other calculations.

Aggregate Functions for SOQL Queries

Let's use properties in a city as an example. Suppose we start with this simple query:

SELECT City__c FROM Property__c

The query returns the city for each of 12 properties.

Because we don't want the entire list of cities, modify the query to use an aggregate function. To get the total number of properties use the COUNT() function.

The aggregate function goes in the SELECT clause of a query, like this:

The parentheses after the function name contain the name of the field to use in the calculation. In this example, we're counting the values in the City__c field.

Run a COUNT() Query

1. In the Query Editor, type:
   

   SELECT City__c FROM Property__c

2. Click Execute.
   Your query results should have 12 rows with the city for each property.
   
3. Edit the query so that the City__c field is inserted in the COUNT() function, like this:
   

   SELECT
   COUNT(City__c)
    FROM Property__c

4. Click Execute.
   Your query result should be 12, the number of properties based on the City__c field.
   
   
   

If you noticed that all of the 12 properties are in only 2 cities, you might be wondering why the count is 12 instead of 2. That's because the COUNT() function counts all values, which often includes multiple records with the same value.

Suppose we want to assign one broker to all of the properties in one city. How many brokers do we need? Put another way, how many unique cities do the 12 properties represent? To count only unique cities, we need another function. (When you hear “unique,” think “distinct.”)

COUNT_DISTINCT() is similar to COUNT() except that it returns only unique values (no duplicates).

Run a COUNT_DISTINCT Query

1. In the Query Editor, edit the query to use the COUNT_DISTINCT() function, like this:
   

   SELECT COUNT_DISTINCT(City__c) FROM Property__c

2. Click Execute.
   Now your result should be 2, the number of unique City__c values among all properties.
   
   
   

Cambridge, Boston, and Brookline are the only unique values found in the City__c field. Note that the COUNT() and COUNT_DISTINCT() functions are case-sensitive. That means that Cambridge is not a duplicate of cambridge. Cambridge and cambridge are considered two unique values.

MIN(), MAX(), AVG(), and SUM()

Other useful aggregate functions include MIN(), MAX(), AVG(), and SUM().

The MIN() and MAX() functions are straightforward. MIN() finds the minimum (lowest) value and MAX() finds the maximum (highest) value. But there's something unique about these functions. They can handle dates and times as well as numbers.

1. In the Query Editor, type:
   

   SELECT MIN(Date_Listed__c) FROM Property__c

2. Click Execute.
   

The MIN() function returns the earliest date. The MAX() function returns the latest (most recent) date.

The MIN() and MAX() functions also recognize the sort order of picklist values, so you can find the first or last value in a picklist.

The AVG() function calculates the average of all values. The SUM() function calculates the sum of all values. Because both of these functions perform calculations, they work only with fields that contain numeric values.

Group the Results of Aggregate Functions

After calculating values based on aggregated data, you might want to report results in groups, as you do in summary reports. To group values in a SOQL query, use the GROUP BY clause. For instance, a customer of DreamHouse Realty wants to know which brokers have sold (closed) a property.

Get Hands-on with the GROUP BY Clause

Next, use the GROUP BY clause in a query.

1. In the Query Editor, type:
   

   SELECT MAX(Status__c), Broker__r.Name
   FROM Property__c
   GROUP BY Broker__r.Name

2. Click Execute.
   Your results should have eight rows.
   
   
   

Remember, this query groups properties by broker. For each broker's properties, we list the most advanced Status__c value. That's the Status picklist value that is closest to the final picklist value. We want brokers who have sold properties, so we'll narrow the results even more. Back to the drawing board.

Filter the Results of Aggregate Functions

Another way to narrow results is to use a HAVING clause. The HAVING clause filters the results returned by an aggregate function.

Hmm… this sounds like something we can use to find brokers who sold properties. Our previous query returned a list of brokers who have properties in any status. Try using the HAVING clause to narrow those results.

1. In the Query Editor, type:
   

   SELECT MAX(Status__c), Broker__r.Name
   FROM Property__c
   GROUP BY Broker__r.Name
   HAVING MAX(Status__c) = 'Closed'

2. Click Query. You should get a single broker.
   
   
   

Yes! There we go. Using the HAVING clause narrows results to properties with the Closed status. Great work!

The WHERE clause is a lot like the HAVING clause, right? Here's how to decide when to use WHERE and when to use HAVING.

• The WHERE clause filters records in a SOQL query that has no aggregate function.
  
• The HAVING clause filters the results after data is aggregated by an aggregate function.
  

As you learned in this module, SOQL is a great tool for accessing data in your Salesforce org. As you continue to build and expand your Apex coding skills, you'll encounter many opportunities to use your SOQL knowledge. As you do, remember that you can find details, examples, and more information about Apex and SOQL in the resources provided in this module. Now, go forth and query… with SOQL.

Resources

• Salesforce Developer Documentation: Using Apex Variables in SOQL and SOSL Queries
  
• Salesforce Developer Documentation: Aggregate Functions
  
• Salesforce Developer Documentation: Example SELECT Clauses
  
• Salesforce Developer Documentation: GROUP BY