SOQL AVG Unexpected Behaviour, Incorrect Result?

Question:

I am working on the Advanced Apex Testing module in Trailhead and encountered an unexpected behavior when using AVG(Amount) in a SOQL query.

Here’s the scenario:

I have one Account and one Opportunity linked to it.
The Opportunity has a Stage of “Prospecting” and an Amount of 1000.00.
In my test, I run the following query:

SELECT AVG(Amount) 
FROM Opportunity 
WHERE AccountId = :testAccountId
WITH USER_MODE

I expect the result to be 1000.00, but instead, it returns 1136.36.
I removed all other data from my Org and re-ran the query, but the result remains the same.

When I manually query all Opportunities and calculate the average, I get the correct result (1000.00), but SOQL’s AVG() function still returns 1136.36.

There are no triggers on Opportunity, and @SeeAllData=true is not used anywhere in my test classes. What could be causing this discrepancy?

Boost your Salesforce career with expert-led Salesforce training in Chennai. Our Salesforce courses offer hands-on projects and personalized guidance, catering to both beginners and experienced professionals.

Answer:

The most likely cause of this issue is currency conversion. Salesforce applies currency conversions based on different factors, such as:

1. The running user’s currency
2. The Opportunity record’s CurrencyIsoCode
3. The Org’s default currency and exchange rates

It appears that your result (1136.36) is being inflated by 7.3529%. This matches a potential exchange rate discrepancy, possibly EUR to USD (exchange rate 1:1.136).

To confirm if currency conversion is affecting the result, check the CurrencyIsoCode of the Opportunity and the user’s currency settings by running:

SELECT Id, Name, CurrencyIsoCode FROM User WHERE Id = :UserInfo.getUserId();
SELECT Id, Amount, CurrencyIsoCode FROM Opportunity WHERE AccountId = :testAccountId;
SELECT IsoCode, ConversionRate FROM DatedConversionRate WHERE IsoCode = 'EUR';

1. Query for the Running User’s Currency

SELECT Id, Name, CurrencyIsoCode FROM User WHERE Id = :UserInfo.getUserId();

Explanation:
This SOQL query retrieves information about the current user (the user running the test or query) from the User object.

Id: The unique identifier of the user.
Name: The name of the user.
CurrencyIsoCode: The currency associated with the user, which is important for currency conversions in Salesforce. The result shows the currency code, such as USD or EUR.

This query helps you determine what currency is associated with the running user, which Salesforce uses for currency conversion during SOQL queries.

See also: Workaround for Offset 2000 limit on SOQL Query

2. Query for the Opportunity’s CurrencyIsoCode

SELECT Id, Amount, CurrencyIsoCode FROM Opportunity WHERE AccountId = :testAccountId;

Explanation:
This SOQL query retrieves Opportunity records related to a specific Account (referenced by the :testAccountId parameter).

Id: The unique identifier of the Opportunity record.
Amount: The monetary value of the Opportunity.
CurrencyIsoCode: The currency associated with the Opportunity record, which might differ from the running user’s currency. This field is critical for determining if currency conversion needs to occur.

If the CurrencyIsoCode of the Opportunity is different from the user’s currency, Salesforce will automatically convert the Opportunity’s amount based on the conversion rate, which may explain discrepancies in aggregate calculations like the AVG(Amount) function.

See also: Can LIKE Work with Multiple Values in SOQL Queries?

3. Query for Dated Conversion Rate

SELECT IsoCode, ConversionRate FROM DatedConversionRate WHERE IsoCode = 'EUR';

Explanation:
This SOQL query retrieves the currency conversion rate from the DatedConversionRate object for a given currency (in this case, 'EUR').

IsoCode: The currency code (e.g., 'EUR' for Euro).
ConversionRate: The exchange rate used to convert from the source currency (e.g., EUR) to the target currency (e.g., USD).

Salesforce stores the currency conversion rates between different currencies, and this query can help determine if an exchange rate is applied that could cause the inflated average you are seeing.

See also: How to Convert Set for Dynamic SOQL IN Clause?

4. Creating an Opportunity with Explicit CurrencyIsoCode

Opportunity testOpportunity = new Opportunity(
    Name = 'Test Opp',
    StageName = 'Prospecting',
    Amount = 1000,
    CloseDate = Date.today(),
    CurrencyIsoCode = 'USD' // Ensure this matches the expected currency
);
insert testOpportunity;

Explanation:
This piece of code creates a new Opportunity record with the following fields:

Name: The name of the Opportunity. In this case, 'Test Opp'.
StageName: The stage of the Opportunity (e.g., 'Prospecting').
Amount: The monetary value of the Opportunity (e.g., 1000).
CloseDate: The close date for the Opportunity (set to today’s date using Date.today()).
CurrencyIsoCode: This field explicitly sets the CurrencyIsoCode for the Opportunity. By setting it to 'USD', we ensure that the Opportunity is recorded in the USD currency, which prevents Salesforce from performing any automatic currency conversion based on the user’s currency.

This explicit currency assignment helps to control the test data and ensures that currency conversion does not inadvertently affect the results of the aggregate query like AVG(Amount).

By using this approach, you can avoid unexpected behavior due to currency discrepancies and get the expected result for your AVG() calculation.

See also: SOQL vs. SQL

Solution:

1. Check the running user’s currency in Setup → Users → User Details.
2. Verify the Opportunity’s CurrencyIsoCode field. If it’s different from the user’s currency, SOQL automatically converts it based on the org’s exchange rates.
3. Ensure your test data explicitly sets the CurrencyIsoCode to match your expected calculations. Modify the test setup as follows:

Opportunity testOpportunity = new Opportunity(
    Name = 'Test Opp',
    StageName = 'Prospecting',
    Amount = 1000,
    CloseDate = Date.today(),
    CurrencyIsoCode = 'USD' // Ensure this matches the expected currency
);
insert testOpportunity;

If you confirm that currency conversion is the issue, either update your test data to explicitly set a consistent currency or account for the exchange rate in your expected results.

See also: Which One Is Better: Loop with SOQL or Create a List Then Loop?

Transform Your Career with Salesforce Training in Chennai

Our Salesforce training goes beyond technical expertise – we focus on preparing you for success in the job market. We emphasize certification exam preparation and interview readiness, equipping you with the tools and confidence to excel in your career. With personalized mentorship, interactive classes, and expert guidance, we help you build a strong foundation in Salesforce, increasing your job readiness and enhancing your employment opportunities.

Unlock new career opportunities with our premier Salesforce training in Chennai.. Our comprehensive program is designed to provide you with a deep understanding of key Salesforce concepts. Whether you aim to become a Salesforce Admin, Developer, or AI expert, our well-structured curriculum offers a balanced blend of theoretical knowledge and practical experience. Through hands-on exercises and in-depth learning materials, you’ll acquire the skills needed to tackle real-world challenges and confidently advance your career.

Enroll in a demo session today and take the first step toward a rewarding Salesforce career in Chennai.!