Preface
In Force.com, there is a Salesforce specific query language called SOQL (Salesforce Object Query Language), which is used for accessing a database from a program. As sentence structures of SOQL are almost the same as SQL, there would not be much trouble in learning SOQL if you have any experience with SQL.
The most common use of SOQL is to specify a condition to obtain a record from an object, which is the database of Force.com.
For example, if you want to obtain records of which the rating is “potential” on Apex, write the code as below:
Account[] accounts = [SELECT Name, Phone
FROM Account WHERE Rating = ‘Potential’];
Just like SQL, you can use the aggregate function feature and GROUP BY in SOQL. In addition, SOQL supports other useful functions such as “Obtain a record by the fiscal year set on Force.com”. In this article, I would like to introduce some of the useful functions available in SOQL.
Aggregate Functions
Aggregate functions in SOQL, such as SUM() and MAX() allow you to summarize your data in the query. For example, below is the code for obtaining the aggregate sum of closed deals.
AggregateResult[] results = [SELECT SUM(Amount) summary
FROM Opportunity WHERE IsWon = true];
System.debug(‘Total Orders Received=’ + results[0].get(‘summary’));
It is important to note that the result of SOQL including the aggregate function is described as an AggregateResult array. This time functions like GROUP BY are not used so you would have only one result. Thus the first value of the result variable is output in a debug mode. Also, if you don’t give alias (summary part), SOQL automatically returns the aggregate function with an auto-assigned alias, expr(i). (i) is the order of aggregate functions that are not assigned alias. If there is SUM() and AVG() without alias in a query, the first one is named as expr0 and the second one is expr1. However, I do recommend you to give alias, unless no special reasons.
Pay Attention to the Governor Limit of Query Rows
One thing we should be aware of is that the governor limit is applied to the query including aggregate function. This includes the record counts returned as a query as well as the record counts in aggregation. I believe the most tricky governor limit when using the aggregate function is query rows limit. The maximum count is 50,000 as of today, so if the number is expected to exceed, you need to think about different ways – such as to give a narrowing-down condition for WHERE and to prepare a summary object. That said, in many cases aggregate functions are useful. Please take advantage of it by considering the requirements and the amount of data. For information on other aggregate functions, refer to here.
GROUP BY and HAVING
Aggregate functions, when utilized together with GROUP BY and HAVING function, offer various ways of aggregation.
GROUP BY
GROUP BY is used when you want to sum up values for each specified value. For example, below is the code for obtaining the number of ongoing opportunities sorted by phase.
AggregateResult[] results = [SELECT StageName, SUM(Amount) summary FROM Opportunity WHERE IsClosed = false GROUP BY StageName]; for(AggregateResult ar: results){ System.debug(‘Phase=’+ ar.get(‘StageName’) + ‘: Price=’+ ar.get(‘summary’)); }
The result of debugging is as follows: (The name of the phase and the amount are dependent on the organization).
Phase=Contact：Pricing=999999
Phase=Evaluation：Price=999999
Phase=Hearing Needs：Price=999999
Phase=Demo/Presentation Price=999999
Phase=Budget/Confirmed by Decision Maker：Price=999999
Phase=Price negotiation：Amount=999999
In SOQL, you only had one AggregateResult. However, when GROUP BY is used, you get multiple results according to the specified values. In the sample above, the results were output by the loop.
HAVING
HAVING is a clause that you can use along with GROUP BY to filter the results. HAVING is similar to WHERE in terms of narrowing-down function, however, WHERE is before and HAVING is after. In the following sample code, group the values with the lead source in opportunities and display the amount sorted by the lead source of which the record count is more than 20.
AggregateResult[] results = [SELECT LeadSource, SUM(Amount) summary FROM Opportunity GROUP BY LeadSource HAVING Count(LeadSource) > 20]; for(AggregateResult ar: results){ System.debug(‘Lead Source=’+ ar.get(‘LeadSource’) + ‘: Price=’+ ar.get(‘summary’)); }
The debugging result is as follows (The lead source name and the amount are dependent on the organization.). Lead Source = Seminar：Amount=999999 Lead Source = News Paper Ads：Amount=999999
Date/Time Function
A Date/Time Function returns the value that represents a particular date. It is useful when you group or filter data into various date periods. For example, “CALENDAR_MONTH(CreatedDate)” returns the numeric value of the created month. “FISCAL_QUARTER(CloseDate)” returns the numeric value of the fiscal quarter. Below is a sample code for obtaining records in a particular fiscal year.
Account[] accounts = [SELECT Name FROM Account WHERE CALENDAR_YEAR(CreatedDate) = 2012];
By using together with GROUP BY, you can sum up the sales by fiscal year.
AggregateResult[] results = [SELECT FISCAL_YEAR(CloseDate) year, SUM(Amount) summary FROM Opportunity WHERE IsWon = true GROUP BY FISCAL_YEAR(CloseDate)]; for(AggregateResult ar: results){ System.debug(‘Fiscal Year=’+ ar.get(‘year’) + ‘: Price=’+ ar.get(‘summary’));}
The debug result is as follows:
Year=2009：Amount=999999
Year=2010：Amount=999999
Year=2011：Amount=999999
Year=2012：Amount=999999
Date Literal
Date literal can be used in the same way as the “date relative value” as seen in Force.com standard report or view. With this function, you can specify “yesterday”, “this month”, or “the last 90 days” from the execution date. You can extract records to match the conditions.
For example, below is the sample code for obtaining records that were updated this week.
Account[] accounts = [SELECT Name FROM Account WHERE LastModifiedDate = THIS_WEEK];
There are many kinds of date literals available, not just “this week”. Please refer to Salesforce for more details.