SOQL and Relationship Queries last part

Relationship Queries Part 1
Relationship Queries Part 2

Date Functions

SOQL has a list of functions that deal with dates which makes your life a lot easier.

1) CALENDAR functions: CALENDAR_MONTH, CALENDAR_QUARTER, CALENDAR_YEAR
2) DAYDAY : DAY_IN_MONTH, DAY_IN_WEEK, DAY_IN_YEAR, DAY_ONLY
3) FISCAL: FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR
4) HOUR function: HOUR_IN_DAY
5) WEEK function: WEEK_IN_MONTH, WEEK_IN_YEAR

SELECT Title FROM Position__c WHERE CALENDAR_MONTH(Date_Closed__c) = 2

 
Aggregate Results with GROUP BY and HAVING

GROUP BY

GROUP BY is used in conjunction with aggregate functions to group the result-set by one or more columns.

SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c GROUP BY Position__r.Department__c

As you can see this query displayed number of Job Applications grouped by Department.

This example would make it more clear.

List aggrs = [SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c
GROUP BY Position__r.Department__c];
for (AggregateResult ja : aggrs){
System.debug(ja.get('dept') + ' | ' + ja.get('total'));
}

 
HAVING

You can use HAVING clause along with GROUP BY clause to filter the results returned by aggregate functions. A HAVING clause is similar to WHERE clause. The difference is you can include aggregate functions in a HAVING clause, but not in WHERE clause.

Lets use the above example again

SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c GROUP BY Position__r.Department__c HAVING COUNT(id)>1

This would return Job Applications GROUP BY Department for which there are more than one Job Applications.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s