Aggregate Functions

Aggregate Functions:A ggregate functions allow you to roll up and summarize your data for analysis. You can use these functions without using a GROUP BY clause.
For example, you could use the AVG() aggregate function to find the average Amount for all your opportunities.

SELECT AVG(Amount) FROM Opportunity 

However, these functions become a more powerful tool to generate reports when you use them with a GROUP BY clause.
For example, you could find the average Amount for all your opportunities by campaign.

 SELECT CampaignId, AVG(Amount)FROM Opportunity GROUP BY CampaignId 

This table lists all the aggregate functions supported by SOQL.

Aggregate Function
Description
AVG()
Returns the average value of a numeric field. For example:

SELECT CampaignId, AVG(Amount) FROM Opportunity 
GROUP BY CampaignId
COUNT() ANDCOUNT(FIELDNAME)
Returns the number of rows matching the query criteria. For example using COUNT():

SELECT COUNT() FROM Account WHERE Name
LIKE 'a%' COUNT() and COUNT(fieldName)

For example using COUNT(fieldName):

SELECT COUNT(Id) FROM Account WHERE Name LIKE 'a%'

Note: COUNT(Id) in SOQL is equivalent to COUNT(*) in SQL.
The COUNT(fieldName) syntax is available in API version 18.0 and later.
If you are using a GROUP BY clause, use COUNT(fieldName) instead of COUNT().
For more information,
https://salesforce23.wordpress.com/read/all-about-soql-sosl/

COUNT_DISTINCT()
Returns the number of distinct non-null field values matching the query criteria. For example:

SELECT COUNT_DISTINCT(Company) FROM Lead

Note: COUNT_DISTINCT(fieldName) in SOQL is equivalent to
COUNT(DISTINCT fieldName) in SQL. To query for all the distinct values, including null, for an object, see GROUP BY.

MIN()
Returns the minimum value of a field. For example:

SELECT MIN(CreatedDate), FirstName, LastName
FROM Contact GROUP BY FirstName, LastName

If you use the MIN() or MAX() functions on a picklist field,
the function uses the sort order of the picklist values instead of alphabetical order.

MAX()
Returns the maximum value of a field. For example:

SELECT Name, MAX(BudgetedCost) FROM Campaign GROUP BY Name
SUM()
Returns the total sum of a numeric field. For example:

SELECT SUM(Amount) FROM Opportunity
WHERE IsClosed = false AND Probability > 60
Group By
Aggregate functions become a more powerful tool to generate reports when you use them with a GROUP BY clause.

SELECT CampaignId, AVG(Amount)FROM Opportunity GROUP BY CampaignId
SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY LeadSource
Having
You can use a HAVING clause with a GROUP BY clause to filter the results returned by aggregate functions, such as SUM(). A HAVING clause is similar to a WHERE clause. The difference is that you can include aggregate functions in a HAVING clause, but not in a WHERE clause.HAVING is a clause which 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.

SELECT LeadSource, COUNT(Name) FROM Lead GROUP BY 
LeadSource HAVING COUNT(Name) > 100

The below query returns accounts with Duplicate Names.

SELECT Name, Count(Id) FROM Account GROUP BY Name HAVING Count(Id) > 1.
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 fiscal quarter.

 SELECT Name FROM Account WHERE CALENDAR_YEAR(CreatedDate) = 2015
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.

 SELECT Name FROM Account WHERE LastModifiedDate = THIS_WEEK

 Question Related to Aggregate Queries:

  1. How many maximum number of aliased fields you can have in Aggregate Query?

Ans: 100

  1. Can we use Database.QueryLocator for Aggregate Queries?

Ans: No

  1. Arrange these in appropriate Order: HAVING,WHERE,LIMIT,GROUP BY,ORDER BY

Ans: WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

  1. What is “GROUP BY ROLLUP” and “GROUP BY CUBE” with Example?

Ans:
GROUP BY ROLLUP:  Add subtotals for aggregated data in query results, A query with a GROUP BY ROLLUP clause returns the same aggregated data as an equivalent query with a GROUP BY clause. It also returns multiple levels of subtotal rows. You can include up to three fields in a comma-separated list in a GROUP BY ROLLUP clause.

 SELECT LeadSource, COUNT(Name) cnt FROM Lead 
GROUP BY ROLLUP(LeadSource)
SELECT Status, LeadSource, COUNT(Name) cnt FROM Lead
GROUP BY ROLLUP(Status, LeadSource)

 GROUP BY CUBE: To add subtotals for every possible combination of grouped field in the query results. This is particular useful if you need to compile cross-tabular reports of your data.

SELECT Type, BillingCountry,GROUPING(Type) grpType, 
GROUPING(BillingCountry) grpCty, COUNT(id) accts FROM Account
GROUP BY CUBE(Type, BillingCountry) ORDER BY GROUPING(Type), 
GROUPING(BillingCountry)

 5. Write a Aggregate Query to get total opportunity amount closed lost ?

Ans. select sum(amount) from opportunity where stagename=’Closed Lost’

  1. Write a Query to get total amount from opportunities close date last three years?

Ans: select sum(amount) from Opportunity where closedate > Last_N_Years : 3

  1. Write a query to get total amount from opportunities with amount greater than 100000 per Account Name in 2015 ?
    Ans:
select sum(amount),account.name from opportunity 
where amount>100000 and calendar_year(account.createddate)=2015
 group by account.name
  1. Write a Query to calculate total opportunity amount by stage expected to close this quarter ?

Ans. Select Sum(Amount) from Opportunity where stagename=’Closed Won’ and CreatedDate=this_quarter group by stageName

  1. Write a query to get total number of accounts which have opportunities

Ans. Select Count(id) from Account where id in (Select AccountId from Opportunity)

  1. How to get the oldest contact “created date” on the Account Object :

Ans. Rollup Summary field with Min(CreatedDate) on Account.

  1. What is the return type of aggregate function?

Ans. AggregateResult , AggregateResult is a read-only sObject and is only used for query results.

  1. Can we use Sub Queries in Aggregate Queries? If so, how?

Ans: No, you are not allowed to use aggregate functions in subqueries.

  1. How we can use Serialization of aggregate query result fields?

Ans. String JSONString = JSON.serialize(Database.Query(‘SELECT AVG(Amount)aver FROM Opportunity’));

  1. Write a Query which will get all unique value in Aggregate Query?
    (SOQL aggregated query for unique values)

Ans:

SELECT Searc_Phrase__c, COUNT_DISTINCT(UserID)
FROM CustomObject__c
GROUP BY Searc_Phrase__c
  1. How you can resolve below error:
    Non-selective query against large object type in trigger

Ans: Optimize the query by adding filter and using external Ids. Also you cna get the fields indexed by Salesforce Support but only when your Query is optimized and result are large more than 1lakh records.

16. Write a soql to get the recent account records ?

List<Account>accs=[Select id,name from account 
Order By createddate DESC limit 50];

17.  Write a Query to obtain the aggregate sum of closed deal?

SELECT SUM(Amount) summary FROM Opportunity WHERE IsWon = true

18. Write a query to SUM up values for each specified value (StageName)?

SELECT StageName, SUM(Amount) summary FROM Opportunity 
WHERE IsClosed = false GROUP BY StageName

19. Write a query to group the values with the lead source in opportunities and display the amount sorted by lead source of which the record count is more than 5?

SELECT LeadSource, SUM(Amount) summary FROM Opportunity 
GROUP BY LeadSource HAVING Count(LeadSource) > 5

20. Write a query for obtaining records in a particular fiscal year?

SELECT Name FROM Account WHERE THIS_FISCAL_YEAR(CreatedDate) = 2015

21. Write a query for sum up the sales by fiscal year? Use Group By in query.

SELECT FISCAL_YEAR(CloseDate) year,SUM(Amount) summary 
FROM Opportunity WHERE IsWon = true GROUP BY FISCAL_YEAR(CloseDate)

22.  Write a query for obtaining records which were updated this week?

SELECT Name FROM Account WHERE LastModifiedDate = THIS_WEEK

23.  Write a query to return sum of payment amount for an Account?

Select Account__c, SUM(Payment_Amount__c) TotAmount 
from Payment group by Account__c

Trigger Example For Aggregate: Click Here

 

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