Start tracking your progress
Trailhead Home
Trailhead Home

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.

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. 

Note

Note

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 App Launcher, 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 the selected price range. The sliders accept values for the maxHomeValue and minHomeValue bind variables.

  3. Move the left slider from 200K to 550K.
  4. Move the right slider from 1.2M to 800K.

Each time a 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.

The sliders set the variable values in the Apex code. Those values replace the variable placeholders in the query.

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

Aggregate Function
Description
Example
COUNT()
Returns the number of rows that are associated with the field
SELECT COUNT(Name)
FROM Broker__c
COUNT_DISTINCT()
Returns the number of unique rows that match the query criteria
SELECT COUNT_DISTINCT(City__c)
FROM Property__c
MIN()
Returns the minimum value of a field
SELECT MIN(Days_On_Market__c)
FROM Property__c
MAX()
Returns the maximum value of a field
SELECT MAX(Beds__c)
FROM Property__c
AVG()
Returns the average value of a numeric field
SELECT City__c, AVG(Days_On_Market__c)
FROM Property__c
GROUP BY City__c
SUM()
Returns the total value of a numeric field
SELECT SUM(Price__c), Broker__r.Name
FROM Property__c
GROUP BY Broker__r.Name

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 18 properties.

Query Results - Total Rows:18. The table has one column, which displays cities.

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:

SELECT COUNT(City__c) FROM Property__c. COUNT is the aggregate function, City__c is the field, and Property__c is the object.

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:
  2. SELECT City__c FROM Property__c
  3. Click Execute.
    Your query results should have 18 rows with the city for each property.
  4. Edit the query so that the City__c field is inserted in the COUNT() function, like this:
  5. SELECT COUNT(City__c) FROM Property__c
  6. Click Execute.
    Your query result should be 18, the number of properties based on the City__c field.
  7. Query Results - Total Rows: 1. The table displays one value: 18.

If you noticed that all of the 18 properties are in only 3 cities, you might be wondering why the count is 18 instead of 3. 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 18 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:
  2. SELECT COUNT_DISTINCT(City__c) FROM Property__c
  3. Click Execute.
    Now your result should be 3, the number of unique City__c values among all properties.
  4. Query Results - Total Rows: 1. Column: count_distinct(City__c) Value 3.

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:
  2. SELECT MIN(Date_Listed__c) FROM Property__c
  3. 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

Before we group some aggregate results, we need to update a few property listings. DreamHouse Realty had an uptick in sales for the week. Update the following properties.

  1. In the DreamHouse app, click the Properties tab.
  2. Click the 30 Peterborough St. property.
  3. Change the Status to Closed.
  4. Click Save.
  5. Repeat steps 2–4 for these properties:
  6. 127 Endicott St.
    650 Kendall St.

The DreamHouse app is up to date. 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.
  3. Query Results - Total Rows: 8, Column 1 - max(Status__c), Column 2 - Name.

 

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:
  2. SELECT MAX(Status__c), Broker__r.Name
    FROM Property__c
    GROUP BY Broker__r.Name
    HAVING MAX(Status__c) = 'Closed'
  3. Click Query.

    You should get a list of three brokers.

  4. Query Results - Total Rows: 3. Column 1: max(Status__c). Column 2: Name.

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